I'm having a problem with some very slow queries that spend a very long time in the 'Sorting result' state and I'm wondering how sorts are implemented in mysql and what I can do to optimize these types of queries.
The query looks something like this:
SELECT col1,col2,col3 from table1 where col1 = 0 ORDER BY col2 DESC LIMIT 10;
Both col1 and col2 have individual indexes (idx_1, idx_2)
Running an EXPLAIN on the query gives me the following information:
table: table1 type: ref possible_keys: idx_1 key: idx_1 key_len: 4 ref: const rows: 10269 extra: where used; Using filesort
I understand that this is retrieving a lot of rows which is slow, but after the rows have been retrieved shouldn't the sort execute very quickly using the index on the sorted column? I notice that the EXPLAIN makes no mention at all of the index on the column being sorted. What is a filesort and what are the different ways that mysql can sort a result set?
thanks,
--brl
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
