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?

Solution 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.

Solution 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

Comments

[Quassnoi]

This solution will just deprive MySQL of possibility to filter on the first two fields and sort on the third one.

[Travis Beale]

He asked why the index wasn't used for sorting.

[Quassnoi]

The index you are suggesting can be used for sorting only. The index proposed by @op can be used for filtering and for sorting. Your index cannot be used for filtering, but may (or may not) be used for sorting.

[Travis Beale]

I agree that's a better solution, I was just trying to answer the very specific question that was asked.

[Muhammad Hasan Khan]

FORCE INDEX solved the problem. The query executes much faster now and you are right about the query matching a large portion of the database.

[Nikhil Sahu]

only adding index number_of_views is not going to solve the problem as that index will be considered which satisfies the where condition. In the OP's answer, index approved_2 should be used and not indexes on approved, public_private and on number_of_views separately.

[Nikhil Sahu]

No. Correct is: If a key is a combination of 3 columns, it will use that particular key when it is using first column or a combination of first two columns or all three columns