Descending Index in MySQL to Speed Up Queries

In this tutorial, we will learn about descending index in MySQL and how to use it to speed up the query performance. So, let’s get started!

Also read: MySQL Index: Show Index

Introduction to MySQL Descending Indexes

In MySQL, we can create and use descending indexes to speed up the performance of queries by storing the key values in descending order.

Let me explain in detail.

In the MySQL versions prior to 8.0, MySQL allows you to mention the DESC in the index definition to specify the descending index. However, MySQL ignores it but it can search the records in reverse order at a high cost.

But now, index key values are stored in descending order and the search is done in the forward order which is more efficient than the previous method.

Additionally, descending indexes let the optimizer to use multiple-column indexes when the most effective scan order combines ascending order for certain columns and descending order for others.

MySQL Descending Index Syntax

To create the descending index, we have to mention the DESC keyword with the column name as shown below-

CREATE INDEX index_name ON table_name(col1 ASC|DESC, col2 ASC|DESC);Code language: SQL (Structured Query Language) (sql)

Here, we can mention the column name as either ascending or descending.

Let’s take an example now.

MySQL Descending Index Examples

Here, we will create a table with four different indexes on it.

DROP TABLE IF EXISTS descDemo;
CREATE TABLE descDemo(
a INT,
b INT
);

CREATE INDEX asc_asc ON descDemo(a ASC,b ASC);
CREATE INDEX asc_desc ON descDemo(a ASC,b DESC);
CREATE INDEX desc_asc ON descDemo(a DESC,b ASC);
CREATE INDEX desc_desc ON descDemo(a DESC,b DESC);Code language: SQL (Structured Query Language) (sql)

Here, we have created four indexes where we have specified the combination of ascending and descending orders of the columns.

Now we will insert hundreds of rows into the table.

You can use the stored procedure to insert the data quickly or simply import the dummy data from the file which you can easily get on the internet.

As of now, we will use stored procedures to insert random values into the table.

DROP PROCEDURE IF EXISTS insertData;
DELIMITER //
CREATE PROCEDURE insertData()
BEGIN
DECLARE counter INT DEFAULT 0;
WHILE counter<=100 DO
SET counter=counter+1;
INSERT INTO descDemo VALUES(
CEILING(RAND()*200),
CEILING(RAND()*100)
);
END WHILE;
END
//
DELIMITER ;
CALL insertData;Code language: SQL (Structured Query Language) (sql)

We are all set now.

Here, the RAND() function will generate a random value and the *200 will make that value come in the range of 1 to 200.

The while loop will help us to iterate the code for the hundred+1 times and insert the data into the table.

Now we will write queries to fetch the data and see the use of descending indexes.

• Fetching the records in ascending order

EXPLAIN SELECT * FROM descDemo;Code language: SQL (Structured Query Language) (sql)

In the above query, the data is ordered in ascending order by default. So, it would use the asc_asc index.

Let’s see the output.

As you can see, the optimizer makes use of the asc_asc index.

• Fetching the records by ascending order of the first column and descending order of the second column.

EXPLAIN SELECT * FROM descDemo ORDER BY a ASC, b DESC;Code language: SQL (Structured Query Language) (sql)

Here you can see, that the optimizer uses the asc_desc index because we have written the query to fetch the records in ascending order of column a and descending order of column b.

• Fetching the records in descending order of the first column and ascending order of the second column.

EXPLAIN SELECT * FROM descDemo ORDER BY a DESC, b;Code language: SQL (Structured Query Language) (sql)

Here, we have changed the order of fetching the records. That’s why the optimizer uses the right index, which is desc_asc.

• Fetching the records in descending order.

EXPLAIN SELECT * FROM descDemo ORDER BY a DESC, b DESC;Code language: SQL (Structured Query Language) (sql)

Here you can see, that we have specified the order as descending to both columns. That’s why the optimizer uses the desc_desc index.

Conclusion

Now you might have gotten the idea about the descending index. From MySQL version 8.0, the performance of descending indexes is improved due to the change in its behaviour and working. This is a pretty useful feature of MySQL that you can use in real-time projects. I hope you find this tutorial helpful. If you do, don’t forget to share it with your friends!

References

MySQL official documentation on descending indexes.