

Master Descending Indexes in MySQL 8.0 for Faster Queries
MySQL 8.0 has come with a list of new features for DBA’s ,we will discuss the new feature in MySQL 8.0 which supports Descending index.Prior to MySQL 8.0 (i.e MySQL 5.6 and 5.7) creating desc index syntax was supported but desc keyword was ignored, Now in MySQL 8.0 release descending index is extended are supported.
What is index?
- Indexes play an important role in performance optimization and they are used frequently to speed up access to particular data and reduce disk I/O operations .
- To understand index easily you can imagine a book,every book has an index with content referring to a page number.If you want to search something in a book you first refer to the index and get the page number and then get the information in the page,like this the indexes in MySQL will tell you the row with matching data.
InnoDB uses a B+Tree structure internally for indexes. A B+Tree is particularly efficient when data doesn’t fit in memory and must be read from the disk, as it ensures that a fixed maximum number of reads would be required to access any data requested, based only on the depth of the tree, So before working with indexes, it is important to understand how indexes work behind the scene and what is the data structure that is used to store these indexes.
B+tree Index:
Indexes are stored on disk in the form of a data structure known as B+tree. B+tree is in many ways similar to a binary search tree. B+tree follows on the same structure as of a binary search tree, in that each key in a node has all key values less than the key as its left children, and all key values more than the key as its right children.
But there are some very important differences,
- B+tree can have more than 1 keys in a node, in fact thousands of keys is seen typically stored in a node and hence, the branching factor of a B+tree is very large and that allows the B+trees to be a lot shallower as compared to their binary search tree counterparts.
- B+trees have all the key values in their leaf nodes. All the leaf nodes of a B+tree are at the same height, which implies that every index lookup will take same number of B+tree lookups to find a value (equisearch)
- Within a B+tree all leaf nodes are linked together in a linked-listed, left to right, and since the values at the leaf nodes are sorted, so range lookups are very efficient.
B+ tree example

Image courtesy :wikipedia
What are Descending indexes?
- A descending index is an index in which the InnoDB stores the entries in descending order and and the optimizer will choose this index when descending order is requested in the query ,which is more efficient for queries with ORDER BY clauses and it need not use a filesort operation.
- Descending indexes are supported only for the InnoDB storage engine.
- By default the indexes are stored in the Ascending order in a B+Tree.
How to add a descending index on a table ?
The keyword DESC is used along with the common index creation syntax ( Alter/Create )
The index (col1_name desc,col2_name asc) satisfies two conditions:
- Order by col1_name desc,col2_name asc : Forward Scan
- Order by col1_name asc,col2_name desc : Backward Scan
Now let us see the query performance based on handlers count for the query with and without indexes
- Here i am using employees table for testing purpose
- Now let us consider an example of the below query with ordering on descending and ascending conditions.
No index were created initially.
The query took 1.30 secs for limit of 10 records,so from above handler Handler_read_rnd_next count we can see ,the query is doing full table scan which is equal to total number of records in table.
- Let us create index according to the order in the query for the columns hire_date and first_name.
- Now the query executes much faster.
We can see from Handler_read_next count,the query is using the index to read the next row in key order and here Handler_read_first suggests that the number of times the first entry in an index was read.
Lets the run the query by changing order to hire_date asc and first_name desc.
As we can see from handler Handler_read_last and Handler_read_prev the index scan is backward way.
In MySQL 8.0 it is a great new feature to avoid filesorting for the queries with Order by desc and asc clause.
More can be read on this official work log WL#1074
Boost Your MySQL Performance! Mydbops offers a comprehensive suite of MySQL database management services, including expert guidance on index strategy and optimization and is listed among the top database experts on TopDevelopers.co.
Contact us today to see how we can help you take your MySQL deployments to the next level!
{{cta}}
FAQs
What is a descending index in MySQL 8.0?
A descending index in MySQL 8.0 allows columns to be indexed in descending order, improving query performance for ORDER BY column DESC
clauses. This is a major enhancement over previous versions where indexes were only created in ascending order.
Why use a descending index in MySQL?
Descending indexes optimize queries that sort data in reverse order. Without them, MySQL might have to perform a filesort operation, which is slower. With a descending index, MySQL can read rows in the desired order directly from the index.
Are descending indexes supported in all storage engines?
No. As of MySQL 8.0, descending indexes are fully supported only in the InnoDB storage engine. Other engines may not support or utilize descending index optimization.
How do I know if MySQL is using my descending index?
Use the EXPLAIN
or EXPLAIN ANALYZE
command to view the query plan. If your descending index is being used, it will be listed under the key
column in the output.