MySQL 8 Cookbook

How to do it...

1. Add a descending index:

mysql> ALTER TABLE employees ADD INDEX name_desc(first_name ASC, last_name DESC);Query OK, 0 rows affected (1.61 sec)Records: 0  Duplicates: 0  Warnings: 0

2. After adding the descending index, the query is able to use the index:

mysql> EXPLAIN SELECT * FROM employees ORDER BY first_name ASC, last_name DESC LIMIT 10\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: employees   partitions: NULL         type: indexpossible_keys: NULL          key: name_desc      key_len: 124          ref: NULL         rows: 10     filtered: 100.00        Extra: NULL

3. The same index can be used for the other way of ordering, that is, ordering by first_name descending and last_name ascending through the backward index scan: