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):

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

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

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/