Optimizing my mysql query to use index for sorting

Question

I have a composite index based on 3 columns, two of which are constrained in my query and the 3rd is in order by clause yet mysql doesn't use index for sorting.

explain select * from videos where public_private='public' and approved='yes' order by number_of_views desc;

+----+-------------+--------+------+--------------------------------+------+---------+------+---------+-----------------------------+
| id | select_type | table  | type | possible_keys                  | key  | key_len | ref  | rows    | Extra     |
+----+-------------+--------+------+--------------------------------+------+---------+------+---------+-----------------------------+
|  1 | SIMPLE      | videos | ALL  | approved,approved_3,approved_2 | NULL | NULL    | NULL | 1476818 | Using where; Using filesort |
+----+-------------+--------+------+--------------------------------+------+---------+------+---------+-----------------------------+

The table structure is as follows:

CREATE TABLE `videos` (
  `indexer` int(9) NOT NULL auto_increment,
  `user_id` int(9) default NULL,
  `public_private` varchar(24) default NULL,
  `approved` varchar(24) default NULL,
  `number_of_views` int(9) default NULL,
  PRIMARY KEY  (`indexer`),
  KEY `approved` (`approved`,`user_id`),
  KEY `approved_3` (`approved`,`public_private`,`indexer`),
  KEY `approved_2` (`approved`,`public_private`,`number_of_views`),
) ENGINE=MyISAM AUTO_INCREMENT=1969091 DEFAULT CHARSET=utf8 |

What should I do to force mysql to use index for sorting the results?

zombat zombat · Accepted Answer · 2009-07-17T18:06:30

I believe that the query you have is probably matching a large percentage of the data in the table. In situations such as this, the MySQL optimizer often chooses to do a table scan and ignore indexes completely, as it is actually faster than going through the trouble of the additional reading of the entire index and using it to pick out the data. So in this case, I'm guessing that public_private='yes' and approved='yes' matches a good portion of your table. Therefore, if MySQL skips using the index because of this, then it's not available for sorting either.

If you really want it to use an index, then the solution would be to use FORCE INDEX:

select * from videos FORCE INDEX (approved_2) where public_private='public' and approved='yes' order by number_of_views desc;

However, I would run some tests to make sure that what you're getting is actually faster than what the MySQL optimizer has chosen to do. Apparently the optimizer does have some issues with making selections for ordering, so you could definitely give this a shot and see if you get improved performance.

Optimizing my mysql query to use index for sorting

5 Answers