MySQL Index Order

This article discusses how to use ascending and descending indexes in MySQL to improve query performance.

In MySQL, you can specify the order of indexes when creating indexes. By default, indexes are stored in ascending order.

MySQL index syntax

To specify the order of indexes, you can create indexes with the following syntax:

CREATE [UNIQUE] INDEX index_name
[USING {BTREE | HASH}]
ON table_name (column_name [ASC | DESC], ...)
[algorithm_option | lock_option];

Or create an index directly when creating a table, as follows:

CREATE TABLE table_name(
  column_list
  INDEX index_name (column_name [ASC | DESC], ...)
);

Here, the ASC specified index is stored in ascending order, which is the default value. The DESC specified index is stored in descending order.

Note that MySQL didn’t really support descending indexes until MySQL 8.0. Although previous versions of MySQL also supported the DESC keyword, MySQL ignores it. This means that, prior to MySQL 8, scanning indexes in descending order was expensive.

MySQL Index Order Examples

First, let’s create a table named test:

CREATE TABLE test (
  v INT,
  INDEX idx_v_asc (v),
  INDEX idx_v_desc (v DESC)
);

Here, we created two indexes:

  • idx_v_asc: store in ascending order
  • idx_v_desc: store in descending order

Then, let’s use the following sql statement to insert 100,000 rows:

INSERT INTO test (v)
SELECT (
    t4.i * 10000 + t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i
  ) v
FROM (
    SELECT 0 i
    UNION
    SELECT 1
    UNION
    SELECT 2
    UNION
    SELECT 3
    UNION
    SELECT 4
    UNION
    SELECT 5
    UNION
    SELECT 6
    UNION
    SELECT 7
    UNION
    SELECT 8
    UNION
    SELECT 9
  ) t0,
  (
    SELECT 0 i
    UNION
    SELECT 1
    UNION
    SELECT 2
    UNION
    SELECT 3
    UNION
    SELECT 4
    UNION
    SELECT 5
    UNION
    SELECT 6
    UNION
    SELECT 7
    UNION
    SELECT 8
    UNION
    SELECT 9
  ) t1,
  (
    SELECT 0 i
    UNION
    SELECT 1
    UNION
    SELECT 2
    UNION
    SELECT 3
    UNION
    SELECT 4
    UNION
    SELECT 5
    UNION
    SELECT 6
    UNION
    SELECT 7
    UNION
    SELECT 8
    UNION
    SELECT 9
  ) t2,
  (
    SELECT 0 i
    UNION
    SELECT 1
    UNION
    SELECT 2
    UNION
    SELECT 3
    UNION
    SELECT 4
    UNION
    SELECT 5
    UNION
    SELECT 6
    UNION
    SELECT 7
    UNION
    SELECT 8
    UNION
    SELECT 9
  ) t3,
  (
    SELECT 0 i
    UNION
    SELECT 1
    UNION
    SELECT 2
    UNION
    SELECT 3
    UNION
    SELECT 4
    UNION
    SELECT 5
    UNION
    SELECT 6
    UNION
    SELECT 7
    UNION
    SELECT 8
    UNION
    SELECT 9
  ) t4
ORDER BY v;

Query OK, 100000 rows affected (1.24 sec)
Records: 100000  Duplicates: 0  Warnings: 0

The following statement retrieves rows in ascending order by column v:

EXPLAIN
SELECT *
FROM test
ORDER BY v;

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | index | NULL          | idx_v_asc | 5       | NULL | 100425 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

The output shows that the statement uses the idx_v_asc index, which corresponds to the ORDER BY v clause in the statement.

The following statement retrieves rows in descending order by column v:

EXPLAIN
SELECT *
FROM test
ORDER BY v DESC;

+----+-------------+-------+------------+-------+---------------+------------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | index | NULL          | idx_v_desc | 5       | NULL | 100425 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

The output shows that the statement uses the idx_v_desc index, which corresponds to the ORDER BY v DESC clause in the statement.

Conclusion

In MySQL, you can specify the order of indexes when creating indexes. By default, indexes are stored in ascending order.