MySQL Descending Index

Summary: in this tutorial, you will learn about MySQL descending index and how to leverage it to increase the performance of queries.

Introduction to MySQL descending index

A descending index is an index that stores key values in the descending order. Before MySQL 8.0, you can specify the DESC in an index definition. However, MySQL ignored it. In the meantime, MySQL could scan the index in reverse order but it comes at a high cost.

The following statement creates a new table with an index:

1 2 3 4 5

CREATE TABLE t(     a INT NOT NULL,     b INT NOT NULL,     INDEX a_asc_b_desc (a ASC, b DESC) );

When you use the SHOW CREATE TABLE in MySQL 5.7, you will find that the DESC is ignored as shown below:

1 2 3 4 5 6 7 8 9

mysql> SHOW CREATE TABLE t\G; *************************** 1. row ***************************        Table: t Create Table: CREATE TABLE `t` (   `a` int(11) NOT NULL,   `b` int(11) NOT NULL,   KEY `a_asc_b_desc` (`a`,`b`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

Starting from MySQL 8.0, the key values are stored in the descending order if you use the DESC keyword in the index definition. The query optimizer can take advantage of descending index when descending order is requested in the query.

The following shows the table structure in MySQL 8.0:

1 2 3 4 5 6 7 8 9

mysql> SHOW CREATE TABLE t\G; *************************** 1. row ***************************        Table: t Create Table: CREATE TABLE `t` (   `a` int(11) NOT NULL,   `b` int(11) NOT NULL,   KEY `a_asc_b_desc` (`a`,`b` DESC) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)

MySQL descending Index example

First, recreate the t table with four indexes in different orders:

1 2 3 4 5 6 7 8 9 10

DROP TABLE t; CREATE TABLE t (     a INT,     b INT,     INDEX a_asc_b_asc (a ASC , b ASC),     INDEX a_asc_b_desc (a ASC , b DESC),     INDEX a_desc_b_asc (a DESC , b ASC),     INDEX a_desc_b_desc (a DESC , b DESC) );

Second, use the following stored procedure to insert rows into the t table:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

CREATE PROCEDURE insertSampleData(     IN rowCount INT,     IN low INT,     IN high INT ) BEGIN     DECLARE counter INT DEFAULT 0;     REPEAT         SET counter := counter + 1;         -- insert data         INSERT INTO t(a,b)         VALUES(             ROUND((RAND() * (high-low))+high),             ROUND((RAND() * (high-low))+high)         );     UNTIL counter >= rowCount     END REPEAT; END$$

The stored procedure inserts a number of rows (rowCount) with the values between low and high into the a and b columns of the t table.

Let’s insert 10,000 rows into the t table with the random values between 1 and 1000:

1	CALL insertSampleData(10000,1,1000);

Third, query data from the t table with different sort orders:

Sort the values in both columns a and b in ascending order:

1 2 3 4 5

EXPLAIN SELECT     * FROM     t ORDER BY a , b; -- use index a_asc_b_asc

Here is the output:

Sort the values in the column  a in ascending order and values in the column b in descending order:

1 2 3 4 5

EXPLAIN SELECT     * FROM     t ORDER BY a , b DESC; -- use index a_asc_b_desc

The output is:

Sort the values in the column  a in descending order and values in the column  b in ascending order:

1 2 3 4 5

EXPLAIN SELECT     * FROM     t ORDER BY a DESC , b; -- use index a_desc_b_asc

The following illustrates the output:

Sort the values in both columns a and b in descending order:

1 2 3 4 5

EXPLAIN SELECT     * FROM     t ORDER BY a DESC , b DESC; -- use index a_desc_b_desc

The following shows the output:

In this tutorial, you have learned how to use the MySQL descending index to improve the query performance.