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?

Answer 1

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.

Answer 2

The order does matter in composite keys. If you want to sort by just number_of_views using the approved_2 key, Change:

KEY `approved_2` (`approved`,`public_private`,`number_of_views`)

to:

KEY `approved_2` (`number_of_views`,`approved`,`public_private`)

Composite keys in MySQL work left-to-right. In the above example, the key declared using number_of_views, approved, and public_private implicity creates indexes on:

• number_of_views
• number_of_views, approved
• number_of_views, approved, public_private