Need help optimizing MYSQL query with join

Question

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 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Could you post the output of EXPLAIN SELECT Item.id, ...? — sfussenegger
– sfussenegger, Commented Apr 8, 2010 at 6:58
And table definitions too, results of show create table favorites query (as well as for items table) — Your Common Sense
– Your Common Sense, Commented Apr 8, 2010 at 7:06
I have added the explain and show create table results above. — makeee
– makeee, Commented Apr 8, 2010 at 7:45

newtover · Accepted Answer · 2010-04-08 09:11:48Z

1

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.

edited Apr 8, 2010 at 9:11

answered Apr 8, 2010 at 8:59

newtover

32.2k11 gold badges89 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

makeee Over a year ago

I'm not seeing why I wouldn't need "ORDER BY Favorites.id". If I leave that out it orders by "Item.id ASC". I don't think indexing on (faver_profile_id, removed) will help much. I can take out Item.removed=0 and Favorite.removed=0 from the query and I still get about the same execution time.

hegemon · Accepted Answer · 2010-04-08 08:18:18Z

0

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?

edited Apr 8, 2010 at 8:18

answered Apr 8, 2010 at 7:11

hegemon

6,8242 gold badges34 silver badges31 bronze badges

1 Comment

makeee Over a year ago

I think you misunderstood. I want results where removed = 0, so I'm using 98% of the table.

Collectives™ on Stack Overflow

Need help optimizing MYSQL query with join

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related