Pre-General Availability Draft: 2017-04-28
MySQL supports descending indexes:
DESC in an
index definition is no longer ignored but causes storage of key
values in descending order. Previously, indexes could be scanned
in reverse order but at a performance penalty. A descending
index can be scanned in forward order, which is more efficient.
Descending indexes also make it possible for the optimizer to
use multiple-column indexes when the most efficient scan order
mixes ascending order for some columns and descending order for
Consider the following table definition, which contains two columns and four two-column index definitions for the various combinations of ascending and descending indexes on the columns:
CREATE TABLE t ( c1 INT, c2 INT, INDEX idx1 (c1 ASC, c2 ASC), INDEX idx2 (c1 ASC, c2 DESC), INDEX idx3 (c1 DESC, c2 ASC), INDEX idx4 (c1 DESC, c2 DESC) );
The table definition results in four distinct indexes. The
optimizer can perform a forward index scan for each of the
ORDER BY clauses and need not use a
ORDER BY c1 ASC, c2 ASC -- optimizer can use idx1 ORDER BY c1 DESC, c2 DESC -- optimizer can use idx4 ORDER BY c1 ASC, c2 DESC -- optimizer can use idx2 ORDER BY c1 DESC, c2 ASC -- optimizer can use idx3
Use of descending indexes is subject to these conditions:
Descending indexes are supported only for the
InnoDBstorage engine, with these limitations:
Change buffering is not supported for a secondary index if the index contains a descending index key column or if the primary key includes a descending index column.
InnoDBSQL parser does not use descending indexes. For
InnoDBfull-text search, this means that the index required on the
FTS_DOC_IDcolumn of the indexed table cannot be defined as a descending index. For more information, see Section 16.8.9, “InnoDB FULLTEXT Indexes”.
Descending indexes are supported for all data types for which ascending indexes are available.
Descending indexes are supported for ordinary (nongenerated) and generated columns (both
DISTINCTcan use any index containing matching columns, including descending key parts.
Descending indexes are supported for
HASHindexes. Descending indexes are not supported for
SPATIALindexes results in an error.