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:

CREATE TABLE t(
    a INT NOT NULL,
    b INT NOT NULL,
    INDEX a_asc_b_desc (a ASC, b DESC)
);Code language: SQL (Structured Query Language) (sql)

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

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)
Code language: SQL (Structured Query Language) (sql)

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

The following shows the table structure in MySQL 8.0:

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)
Code language: SQL (Structured Query Language) (sql)

MySQL Descending Index example

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

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)
);Code language: SQL (Structured Query Language) (sql)

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

DELIMITER $$

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$$    

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

The stored procedure inserts several 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:

CALL insertSampleData(10000,1,1000);Code language: SQL (Structured Query Language) (sql)

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

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

EXPLAIN SELECT 
    *
FROM
    t
ORDER BY a , b; -- use index a_asc_b_ascCode language: SQL (Structured Query Language) (sql)

Here is the output:

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

EXPLAIN SELECT 
    *
FROM
    t
ORDER BY a , b DESC; -- use index a_asc_b_descCode language: SQL (Structured Query Language) (sql)

The output is:

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

EXPLAIN SELECT 
    *
FROM
    t
ORDER BY a DESC , b; -- use index a_desc_b_ascCode language: SQL (Structured Query Language) (sql)

The following illustrates the output:

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

EXPLAIN SELECT 
    *
FROM
    t
ORDER BY a DESC , b DESC; -- use index a_desc_b_descCode language: SQL (Structured Query Language) (sql)

The following shows the output:

Summary

• Use the MySQL descending index to improve query performance.