O'Reilly logo

MySQL 8 Cookbook by Karthik Appigatla

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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
  1. 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
  1. 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:

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required