A few days back we were trying to optimize a sql query that was using an ORDER BY clause. When we ran an EXPLAIN on the query, we saw that the query was Using filesort and Using temporary. Since this query was run quite frequently, there was a benefit in optimizing this.
I had been under the impression that if your query uses filesort and then has to use temp tables for sorting, you cannot do much to save the query. However, that is not the case. If you have an index on the column being used to do the order by , mysql will use that index for sorting. Of course, there are caveats to this, the most important one being
The key used to fetch the rows is not the same as the one used in the ORDER BY:
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
Hence, if you have a query which requires to be sorted, and it is being run quite frequently, it makes sense to add an index on the column on which you are doing the sort.
Comments
No comments