Descending Indexes in MySQL 8.0
In Database Systems, The indexes are data structures to improve the performance of data access and retrieval operations. Indexes helps you to locate the data without having to search every record of the database table. The InnoDB (default storage engine in MySQL) uses B+Tree Index Structure , The advantage of B+Tree indexes is they are capable of automatically reorganizing itself with small, local, changes, in the face of insertions and deletions so reorganization of entire file is not required to maintain performance. B+Tree indexes contains a relatively smaller number of levels (logarithmic in the size of the main file) so searches are highly optimal and that is best suited for modern web-scale database infrastructure operations. MySQL 8 supports now Descending Index and so InnoDB can store data in descending order and this helps optimizer to perform optimally when descending order is requested in the query, Up until the release of MySQL 8, all indexes were created in ascending order (Note: In MySQL 5.6 and 5.7 creating desc index syntax was supported but desc keyword was ignored). In this blog post we have explained how MySQL 8 descending index can influence the performance of SORT / SEARCH query operations:
What are Descending Indexes in MySQL ?
In MySQL 8 with descending indexes the index stores data on a specified column or columns in a descending order with default search from highest to lowest value. Descending indexes are really helpful when a query sorts / search on selected columns ascending and few other in descender order (we have explained below the same with example). Till MySQL-5.7, we use backward index scans or filesort for all queries, Which is super expensive and MySQL 8 has solved this for us with descending indexes.
Testing Descending Indexes on MySQL 8
Below we have explained usage of descending indexes on MySQL 8 by creating table contact_tab and the index (contact_dob desc, contact_name asc):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
mysql> show create table contact_tab\G *************************** 1. row *************************** Table: contact_tab Create Table: CREATE TABLE `contact_tab` ( `contact_id` int NOT NULL, `contact_name` varchar(25) NOT NULL, `contact_dob` date NOT NULL, PRIMARY KEY (`contact_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> mysql> mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.22 | +-----------+ 1 row in set (0.00 sec) mysql> mysql> alter table contact_tab add key dob_desc_cntct_name_asc(contact_dob desc, contact_name asc); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table contact_tab\G *************************** 1. row *************************** Table: contact_tab Create Table: CREATE TABLE `contact_tab` ( `contact_id` int NOT NULL, `contact_name` varchar(25) NOT NULL, `contact_dob` date NOT NULL, PRIMARY KEY (`contact_id`), KEY `dob_desc_cntct_name_asc` (`contact_dob` DESC,`contact_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> |
Understanding how Descending Index works on MySQL 8
The index (contact_dob desc, contact_name asc) which satisfies two conditions (copied below):
- Order by contact_dob desc, contact_name asc : Forward Scan
- Order by contact_dob asc, contact_name desc : Backward Scan
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
mysql> explain select * from contact_tab order by contact_dob desc, contact_name asc\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: contact_tab partitions: NULL type: index possible_keys: NULL key: dob_desc_cntct_name_asc key_len: 105 ref: NULL rows: 5194401512 filtered: 100.00 Extra: Using index 1 row in set, 1 warning (3.18 sec) mysql> explain select * from contact_tab order by contact_dob asc, contact_name desc\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: contact_tab partitions: NULL type: index possible_keys: NULL key: dob_desc_cntct_name_asc key_len: 105 ref: NULL rows:5194401512 filtered: 100.00 Extra: Backward index scan; Using index 1 row in set, 1 warning (3.18 sec) mysql> |
In the example above, index on (contact_dob desc, contact_name asc) can be used to satisfy both contact_dob desc, contact_name asc and contact_dob asc, contact_name desc (same order across two fields). Interestingly both using index and query executed in 3.18 seconds
Testing Descending Indexes on MySQL 5.7
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
mysql> show create table contact_tab\G *************************** 1. row *************************** Table: contact_tab Create Table: CREATE TABLE `contact_tab` ( `contact_id` int(11) NOT NULL, `contact_name` varchar(25) NOT NULL, `contact_dob` date NOT NULL, PRIMARY KEY (`contact_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> mysql> mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.32 | +-----------+ 1 row in set (0.00 sec) mysql> alter table contact_tab add key dob_desc_cntct_name_asc(contact_dob desc, contact_name asc); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table contact_tab\G *************************** 1. row *************************** Table: contact_tab Create Table: CREATE TABLE `contact_tab` ( `contact_id` int(11) NOT NULL, `contact_name` varchar(25) NOT NULL, `contact_dob` date NOT NULL, PRIMARY KEY (`contact_id`), KEY `dob_desc_cntct_name_asc` (`contact_dob`,`contact_name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> explain select * from contact_tab order by contact_dob desc, contact_name asc\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: contact_tab partitions: NULL type: index possible_keys: NULL key: dob_desc_cntct_name_asc key_len: 30 ref: NULL rows: 5194401512 filtered: 100.00 Extra: Using index; Using filesort 1 row in set, 1 warning (57.13 sec) mysql> explain select * from contact_tab order by contact_dob asc, contact_name desc\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: contact_tab partitions: NULL type: index possible_keys: NULL key: dob_desc_cntct_name_asc key_len: 30 ref: NULL rows: 5194401512 filtered: 100.00 Extra: Using index; Using filesort 1 row in set, 1 warning (57.13 sec) mysql> |
In the example above, query does a filesort and response time / latency (57.13 Sec.) is much higher compared to MySQL 8 (3.18 Sec.), This makes descending indexes super expensive on MySQL 5.7
Conclusion
We want to thank MySQL Server Development Team at Oracle for implementing Descending Indexes in MySQL 8 which can improve performance of SORT / SEARCH queries in both ascending and descending order. MySQL 8 has several such features which greatly influence performance and reliability of MySQL Database Infrastructure, If you are curious about MySQL 8 features we strongly recommend the blog from Geir Høydalsvik on MySQL 8 features – https://mysqlserverteam.com/the-complete-list-of-new-features-in-mysql-8-0/
