How do I create a DESC index in MySQL?

Question

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?

Answer 1

That's one of those MySQL "features" where it silently ignores your request to do something because it's simply not implemented:

From http://dev.mysql.com/doc/refman/5.5/en/create-index.html

"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"

Answer 2

MySQL, starting from version 8, supports descending indexes. In earlier versions, the "DESC" clause was silently ignored when creating an index. This was not a problem for (a) single-column indexes or (b) for multi-column indexes where all columns had one direction: either all ASC or all DESC – since indexes are bidirectional.

But if you need a multi-column index where column directions are different, e.g., you run multiple quires like that:

SELECT * from MyTable WHERE ColumnA = 1 ORDER BY ColumnB ASC, ColumnC DESC

you needed the following index: (ColumnA, ColumnB ASC, ColumnC DESC).

You could create an index with these parameters in MySQL before version 8, but it was created silently, in fact, with all columns ascending (ColumnA ASC, ColumnB ASC, ColumnC ASC).

So your query couldn't fully use that index - it only took columns A and B from the index while using unindexed (filesort) data for column C.

This will no longer be an issue in MySQL 8.0 and later versions. See https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html

MySQL version 8 was released for general availability on April 19th, 2018 (v8.0.11).

Answer 3

As mentioned the feature is not implemented but some workarounds may be of interest:

One possibility is to store the field in a negated or reverse value.

If it is a number you can store (-n) or (MAXVAL -n) if unsigned

If it's a date or timestamp, some would advocate to store a number instead and use functions such as FROM_UNIXTIME()

Of course such change is not always easily done... depends on existing code etc.