{"id":6836,"date":"2018-08-12T09:14:01","date_gmt":"2018-08-12T16:14:01","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=6836"},"modified":"2024-01-05T03:20:56","modified_gmt":"2024-01-05T10:20:56","slug":"mysql-descending-index","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-index\/mysql-descending-index\/","title":{"rendered":"MySQL Descending Index"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about MySQL descending index and how to leverage it to increase the performance of queries.<\/p>\n\n\n\n

Introduction to MySQL descending index<\/h2>\n\n\n\n

A descending index is an index that stores key values in the descending order. Before MySQL 8.0, you can specify the DESC<\/code> 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.<\/p>\n\n\n\n

The following statement creates a new table<\/a> with an index:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> t(\n    a INT<\/span> NOT<\/span> NULL<\/span>,\n    b INT<\/span> NOT<\/span> NULL<\/span>,\n    INDEX<\/span> a_asc_b_desc (a ASC<\/span>, b DESC<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
When you use the SHOW CREATE TABLE<\/code> in MySQL 5.7, you will find that the DESC<\/code> is ignored as shown below:<\/p>\n\n\n
mysql> SHOW<\/span> CREATE<\/span> TABLE<\/span> t\\G;\n*************************** 1. row ***************************\n       Table: t\nCreate<\/span> Table<\/span>: CREATE<\/span> TABLE<\/span> `t`<\/span> (\n  `a`<\/span> int<\/span>(11<\/span>) NOT<\/span> NULL<\/span>,\n  `b`<\/span> int<\/span>(11<\/span>) NOT<\/span> NULL<\/span>,\n  KEY<\/span> `a_asc_b_desc`<\/span> (`a`<\/span>,`b`<\/span>)\n) ENGINE<\/span>=InnoDB<\/span> DEFAULT<\/span> CHARSET<\/span>=latin1\n1<\/span> row<\/span> in<\/span> set<\/span> (0.00<\/span> sec)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
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.<\/p>\n\n\n\n
The following shows the table structure in MySQL 8.0:<\/p>\n\n\n
mysql> SHOW<\/span> CREATE<\/span> TABLE<\/span> t\\G;\n*************************** 1. row ***************************\n       Table: t\nCreate<\/span> Table<\/span>: CREATE<\/span> TABLE<\/span> `t`<\/span> (\n  `a`<\/span> int<\/span>(11<\/span>) NOT<\/span> NULL<\/span>,\n  `b`<\/span> int<\/span>(11<\/span>) NOT<\/span> NULL<\/span>,\n  KEY<\/span> `a_asc_b_desc`<\/span> (`a`<\/span>,`b`<\/span> DESC<\/span>)\n) ENGINE<\/span>=InnoDB<\/span> DEFAULT<\/span> CHARSET<\/span>=utf8mb4 COLLATE<\/span>=utf8mb4_0900_ai_ci\n1<\/span> row<\/span> in<\/span> set<\/span> (0.00<\/span> sec)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL Descending Index example<\/h2>\n\n\n\n
First, create the t<\/code> table with four indexes in different orders:<\/p>\n\n\n
DROP<\/span> TABLE<\/span> t;\n\nCREATE<\/span> TABLE<\/span> t (\n    a INT<\/span>,\n    b INT<\/span>,\n    INDEX<\/span> a_asc_b_asc (a ASC<\/span> , b ASC<\/span>),\n    INDEX<\/span> a_asc_b_desc (a ASC<\/span> , b DESC<\/span>),\n    INDEX<\/span> a_desc_b_asc (a DESC<\/span> , b ASC<\/span>),\n    INDEX<\/span> a_desc_b_desc (a DESC<\/span> , b DESC<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
DELIMITER $$\n\nCREATE<\/span> PROCEDURE<\/span> insertSampleData(\n    IN<\/span> rowCount INT<\/span>, \n    IN<\/span> low<\/span> INT<\/span>, \n    IN<\/span> high<\/span> INT<\/span>\n)\nBEGIN<\/span>\n    DECLARE<\/span> counter INT<\/span> DEFAULT<\/span> 0<\/span>;\n    REPEAT\n        SET<\/span> counter := counter + 1<\/span>;\n        -- insert data<\/span>\n        INSERT<\/span> INTO<\/span> t(a,b)\n        VALUES<\/span>(\n            ROUND<\/span>((RAND<\/span>() * (high<\/span>-low<\/span>))+high<\/span>),\n            ROUND<\/span>((RAND<\/span>() * (high<\/span>-low<\/span>))+high<\/span>)\n        );\n    UNTIL counter >= rowCount\n    END<\/span> REPEAT<\/span>;\nEND<\/span>$$    \n\nDELIMITER ;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The stored procedure inserts several rows (rowCount<\/code>) with the values between low<\/code> and high<\/code> into the a<\/code> and b<\/code> columns of the t<\/code> table.<\/p>\n\n\n\n
Let’s insert 10,000<\/code> rows into the t<\/code> table with the random values between 1 and 1000:<\/p>\n\n\n
CALL<\/span> insertSampleData(10000<\/span>,1<\/span>,1000<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, query data from the t<\/code> table with different sort orders:<\/p>\n\n\n\n
Sort the values in both columns a and b in ascending order:<\/p>\n\n\n
EXPLAIN<\/span> SELECT<\/span> \n    *\nFROM<\/span>\n    t\nORDER<\/span> BY<\/span> a , b; -- use index a_asc_b_asc<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
Sort the values in the column\u00a0a<\/code>\u00a0in ascending order and values in the column\u00a0b<\/code>\u00a0in descending order:<\/p>\n\n\n
EXPLAIN<\/span> SELECT<\/span> \n    *\nFROM<\/span>\n    t\nORDER<\/span> BY<\/span> a , b DESC<\/span>; -- use index a_asc_b_desc<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output is:<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
Sort the values in the column\u00a0a<\/code>\u00a0in descending order and values in the column \u00a0b<\/code>\u00a0in ascending order:<\/p>\n\n\n
EXPLAIN<\/span> SELECT<\/span> \n    *\nFROM<\/span>\n    t\nORDER<\/span> BY<\/span> a DESC<\/span> , b; -- use index a_desc_b_asc<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following illustrates the output:<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
Sort the values in both columns a<\/code> and b<\/code> in descending order:<\/p>\n\n\n
EXPLAIN<\/span> SELECT<\/span> \n    *\nFROM<\/span>\n    t\nORDER<\/span> BY<\/span> a DESC<\/span> , b DESC<\/span>; -- use index a_desc_b_desc<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following shows the output:<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the MySQL descending index to improve query performance.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Second, use the following stored procedure<\/a> to insert<\/a> rows into the t<\/code> table:<\/p>\n\n\n