Need help optimizing MYSQL query with join

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;

+----+-----开发者_如何学运维--------+----------+--------+----------------------------------------------------- ----------+------------------+---------+-----------------------------------------+------+-------------+
| id | select_type | table    | type   | possible_keys                                                 | key              | key_len | ref                                     | rows | Extra       |
+----+-------------+----------+--------+---------------------------------------------------------------+------------------+---------+-----------------------------------------+------+-------------+
|  1 | SIMPLE      | Favorite | ref    | notice_id,faver_profile_id,collection_id_idx,idx_faver_idx_id | idx_faver_idx_id |       4 | const                                   | 7910 | Using where |
|  1 | SIMPLE      | Item     | eq_ref | PRIMARY                                                       | PRIMARY          |       4 | gragland_imgfavebeta.Favorite.notice_id |    1 | Using where |
+----+-------------+----------+--------+---------------------------------------------------------------+------------------+---------+-----------------------------------------+------+-------------+

+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| favorites | CREATE TABLE `favorites` (
             `id` int(11) NOT NULL auto_increment COMMENT 'unique identifier',
             `faver_profile_id` int(11) NOT NULL default '0',
             `collection_id` int(11) default NULL,
             `collection_order` int(8) default NULL,
             `created` datetime NOT NULL default '0000-00-00 00:00:00' COMMENT 'date this record was created',
             `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
             `notice_id` int(11) NOT NULL default '0',
             `removed` tinyint(1) NOT NULL default '0',
              PRIMARY KEY  (`id`),
              KEY `notice_id` (`notice_id`),
              KEY `faver_profile_id` (`faver_profile_id`),
              KEY `collection_id_idx` (`collection_id`),
              KEY `idx_faver_idx_id` (`faver_profile_id`,`id`)
              ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+


+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| items |CREATE TABLE `items` (
         `id` int(11) NOT NULL auto_increment COMMENT 'unique identifier',
         `submitter_id` int(11) NOT NULL default '0' COMMENT 'who made the update',
         `source_image` varchar(255) default NULL COMMENT 'update content',
         `cached_image` varchar(255) default NULL,
         `source_title` varchar(255) NOT NULL default '',
         `source_url` text NOT NULL,
         `width` int(4) NOT NULL default '0',
         `height` int(4) NOT NULL default '0',
         `status` varchar(122) NOT NULL default '',
         `popular` int(1) NOT NULL default '0',
         `made_popular` timestamp NULL default NULL,
         `fave_count` int(9) NOT NULL default '0',
         `tags` text,
         `user_art` tinyint(1) NOT NULL default '0',
         `nudity` tinyint(1) NOT NULL default '0',
         `created` datetime NOT NULL default '0000-00-00 00:00:00' COMMENT 'date this record was created',
         `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'date this record was modified',
         `removed` int(1) NOT NULL default '0',
         `nofront` tinyint(1) NOT NULL default '0',
         `test` varchar(10) NOT NULL default '',
         `recs` text,
         `recs_data` text,
         PRIMARY KEY  (`id`),
         KEY `notice_profile_id_idx` (`submitter_id`),
         KEY `content` (`source_image`),
         KEY `idx_popular` (`popular`),
         KEY `idx_madepopular` (`made_popular`),
         KEY `idx_favecount_idx_id` (`fave_count`,`id`)
         ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

First of all, you order by favorites.id which is clustered primary key in favorites table. This wil not be necessary of you will join favorites to items instead of items to favorites.

Second, (Item.removed = '0') in WHERE is excess, because the same condition has already been used in JOIN.

Third, change the order of condition in join to:

`Favorite`.`notice_id` = `Item`.`id` AND `Item`.`removed` = 0

the optimizer will be able to use you primary key for index. You may even consider creating (id, removed) index on items table.

Next, create (faver_profile_id, removed) index in favorites (or better update faver_profile_id index) and change the order of conditions in WHERE to the following:

(`faver_profile_id` = 1)
AND (`Favorite`.`removed` = 0)
AND (`collection_id` IS NULL)

UPD: I am sorry, I missed that you already join favorites to items. Then the ORDER BY is not needed. You should result in something like the following:

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 (`Favorite`.`notice_id` = `Item`.`id` AND `Item`.`removed` = 0) 
WHERE `faver_profile_id` = 1
    AND `Favorite`.`removed` = 0
    AND `collection_id` IS NULL
LIMIT 50;

And one more thing, when you have KEY idx_faver_idx_id (faver_profile_id,id) you do not need KEY faver_profile_id (faver_profile_id), because the second index just duplicates half of the idx_faver_idx_id. I hope you will extend the second index, as I suggested.

Get a copy of your table from backup, and try to make an index on Favorite table covering all WHERE and JOIN conditions, namely (removed, collection_id, profile_id). Do the same with Item. It might help, but will make inserts potentially much slower.

The SQL engine won't use an index if it still has to do full table scan due to constraints, would it?