I'm doing a join between the "favorites" table (3 million rows) the "items" table (600k rows).
The query is taking anywhere from .3 seconds to 2 seconds, and I'm hoping I can optimize it some.
Favorites.faver_profile_id and Items.id are indexed.
Instead of using the faver_profile_id index I created a new index on (faver_profile_id,id), which eliminated the filesort needed when sorting by id. Unfortunately this index doesn't help at all and I'll probably remove it (yay, 3 more hours of downtime to drop the index..)
Any ideas on how I can optimize this query?
In case it helps:
Favorite.removed and Item.removed are "0" 98% of the time.
Favorite.collection_id is NULL about 80% of the time.
SELECT `Item`.`id`, `Item`.`source_image`, `Item`.`cached_image`, `Item`.`source_title`, `Item`.`source_url`, `Item`.`width`, `Item`.`height`, `Item`.`fave_count`, `Item`.`created`
FROM `favorites` AS `Favorite`
LEFT JOIN `items` AS `Item`
ON (`Item`.`removed` = 0 AND `Favorite`.`notice_id` = `Item`.`id`)
WHERE ((`faver_profile_id` = 1) AND (`collection_id` IS NULL) AND (`Favorite`.`removed` = 0) AND (`Item`.`removed` = '0'))
ORDER BY `Favorite`.`id` desc LIMIT 50;
