mulitiple-column index => why filesort

[Anon]

I created a mutilple-column index say,

"create index col1_col2 on table_name (col1,col2) "

Both col1 and col2 are mediumint unsigned.

"explain select * from table_name where col1='value' order by col1 DESC,col2 DESC "

shows that the query is still using filesort !
This is not supposed the be the case. Mysql manual says it should read the index backward.

what is happening here? anyone know?
It's MySql version 3.23.23. Not including "DESC" doesn't use any filesort.

many thanks

[tomhath]

Just a guess, but that often happens if less than a few thousand rows are involved. It has to read them all anyway, so it skips using the index and sorts in memory after it reads the rows. How many rows are involved here?

[Anon]

There are ~ 5700 rows.
col1's values ~ [1,1000]
col2's values ~ [1,100]

There is not much the problem for the moment. But soon the number of rows will grow rapidly and there will be 1000 rows to sort for a particular value of col1 (if index is not read backward).

I created a test table with around 50000 rows of random data. Querying with and without the "DESC" took 0.500 and 0.005 s respectively. The actual time taken varied a little, but their ratio is quite consistent. .