Descending Indexes
Descending index is mainly used to reduce sorting and remove filesort.
MySQL supports descending index: DESC in index definition is no longer ignored, but key values are stored in descending order. Previously, indexes could be scanned in reverse order, but performance would be affected. It can scan the descending index in forward order, which is more efficient. When the most efficient scan order mixes the ascending order of some columns with the descending order of other columns, the descending index also enables the optimizer to use multiple column indexes.
mysql> 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) -> ); Query OK, 0 rows affected (0.40 sec) mysql> insert into t values(1,1),(1,2),(1,3),(1,4),(1,5), -> (2,1),(2,2),(2,3),(2,4),(2,5), -> (3,1),(3,2),(3,3),(3,4),(3,5), -> (4,1),(4,2),(4,3),(4,4),(4,5), -> (5,1),(5,2),(5,3),(5,4),(5,5); Query OK, 25 rows affected (0.12 sec) Records: 25 Duplicates: 0 Warnings: 0 mysql> desc select * from t ORDER BY c1 ASC, c2 ASC; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t | NULL | index | NULL | idx1 | 10 | NULL | 25 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec) mysql> desc select * from t ORDER BY c1 DESC, c2 DESC; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t | NULL | index | NULL | idx4 | 10 | NULL | 25 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> desc select * from t ORDER BY c1 ASC, c2 DESC; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t | NULL | index | NULL | idx2 | 10 | NULL | 25 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> desc select * from t ORDER BY c1 DESC, c2 ASC; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t | NULL | index | NULL | idx3 | 10 | NULL | 25 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
Reference link