How do I create a DESC index in MySQL?
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).
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"
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.