I have a table from which I need to obtain rows ordered by a field in descending order. When running an EXPLAIN query like the following:
EXPLAIN SELECT ... FROM table WHERE ... ORDER BY field DESC
I get Using where; Using filesort in the Extra column. So I try to create a DESC index:
CREATE INDEX name ON table (field DESC);
But when I run EXPLAIN again, I get the same Using where; Using filesort in the Extra column and the performance is pretty much the same.
What am I doing wrong?
解决方案
That's one of those MySQL "features" where it silently ignores your request to do something because it's simply not implemented:
"An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order"