Why is the index on s not used for sort here?

Question

Setup:

mysql> create table test(id integer unsigned,s varchar(30));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test(id,s) value(1,'s');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test(id,s) value(1,'tsr');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test(id,s) value(1,'ts3r');
Query OK, 1 row affected (0.00 sec)

mysql> create index i_test_id on test(id);
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create index i_test_s on test(s);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>  insert into test(id,s) value(21,'ts3r');
Query OK, 1 row affected (0.00 sec)

And then run this:

mysql> explain select * from test where id in (1) order by s desc;
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key       | key_len | ref   | rows | Extra                       |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | test  | ref  | i_test_id     | i_test_id | 5       | const |    2 | Using where; Using filesort |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------------+
1 row in set (0.02 sec)

We can see it uses filesort instead of using the index on s,which will be slow when the selected result set is big.How to optimize it?

Answer 1

The index on id is being used to identify the rows to return. Depending on the version of MySQL you are using, it may only allow the use of one index per table, and the optimizer has determined it is more efficient to use the index for filtering the rows rather than for ordering.

Answer 2

Sometimes MySQL does not use an index, even if one is available. One circumstance under which this occurs is when the optimizer estimates that using the index would require MySQL to access a very large percentage of the rows in the table.

From: MySQL 5.1 Reference Manual: How MySQL Uses Indexes

Answer 3

Create a clustered index on the column 'id'. Clustered index means a physical sort. That way I am guessing there wont be a filesort, when this query is invoked.

But a table can have only one clustered index. Hence , if you have another column that is a primary key for the table, you may not be able to create a clustered index on column 'id'. As primary keys by default are clustered.

Answer 4

What version of MySQL are you on? Not until version 5 could MySQL use more than one index per table.

The choice of the indexes to use also depends on the size of the result set. With only two records returned in the result, it may not use the index anyway. For such small result sets, MySQL doesn't seem to mind sorting things manually.

However, what you could do to really help MySQL out, if this is a common query for you, is to add a compound index ('id', 's'). Basically, it's almost like your creating another little table that is always sorted by id then s, so no filesort would be required, and it would only need the one index, not two.