Prior to MySQL 8, an index definition could contain the order (ascending or descending), but it was only parsed and not implemented. The index values were always stored in ascending order. MySQL 8.0 introduced support for descending indexes. Thus, the specified order in the index definition is not ignored. A descending index actually stores key values in descending order. Remember that scanning an ascending index in reverse is not efficient for a descending query.
Consider a case where, in a multi-column index, you can specify certain columns to be descending. This can help for queries wherein we have both ascending and descending ORDER BY
clauses.
Suppose you want to sort the employees
table with first_name
ascending and last_name
descending; MySQL cannot use the index on first_name
and last_name
. Without a descending index:
mysql> SHOW CREATE TABLE employees\G *************************** 1. row *************************** Table: employees Create Table: CREATE TABLE...