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

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 a开发者_StackOverflow社区ffected (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?

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

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.

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.

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.

The problem you are experiencing is coming from the fact that you are putting an Order by clause in your sql statement. This is causing MySql to skip using any of the indexes and doing a full sort on S. The explain statement is showing that MySql has the i_test_id a possible index to choose from and the key field is showing that it has been chosen, but it must perform a sort on s as well. The optimizer has chosen to not use i_test_s as a possible index because it would be more costly in term of performance. You can go around this issue by building componsite indexes at the expense of disk space, or you can structure your query differently using Unions instead. Haven't tried it in your example though.