Order of Indexes

Although the CREATE INDEX allows specifying the ORDER, ASC or DESC it does not matter on an index, since the travesal is the same.

Show indexes

mysql> SHOW INDEX FROM film FROM sakila;

+-------+------------+-----------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+-------+------------+-----------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| film | 0 | PRIMARY | 1 | film_id | A | 1000 | NULL | NULL | | BTREE | | |

| film | 1 | idx_title | 1 | title | A | 1000 | NULL | NULL | | BTREE | | |

| film | 1 | idx_fk_language_id | 1 | language_id | A | 1 | NULL | NULL | | BTREE | | |

| film | 1 | idx_fk_original_language_id | 1 | original_language_id | A | 1 | NULL | NULL | YES | BTREE | | |

+-------+------------+-----------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Query the film table

mysql> EXPLAIN SELECT film_id, length

 -> FROM film

 -> WHERE length = 100;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | film | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 10.00 | Using where |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

You can notice no index is being used.

Add an index on film

mysql> CREATE INDEX idx_film_length ON film (length);

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

and re-run the EXPLAIN again

mysql> EXPLAIN SELECT film_id, length

 -> FROM film

 -> WHERE length = 100;

+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+

| 1 | SIMPLE | film | NULL | ref | idx_film_length | idx_film_length | 3 | const | 12 | 100.00 | Using index |

+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+

You will notice our newly created index is now being used.

Create another index and specify order

This new indexed is ordered by descending order

mysql> CREATE INDEX idx_film_length_desc ON film (length DESC);

Query OK, 0 rows affected, 1 warning (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 1

re-run the EXPLAIN

mysql> EXPLAIN SELECT film_id, length

 -> FROM film

 -> WHERE length = 100;

+----+-------------+-------+------------+------+--------------------------------------+-----------------+---------+-------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+------+--------------------------------------+-----------------+---------+-------+------+----------+-------------+

| 1 | SIMPLE | film | NULL | ref | idx_film_length,idx_film_length_desc | idx_film_length | 3 | const | 12 | 100.00 | Using index |

+----+-------------+-------+------------+------+--------------------------------------+-----------------+---------+-------+------+----------+-------------+

Notice they are now two possible indexes to satisfy this query, the idx_file_length and idx_film_length_desc. The optimize does chose the first one, which is by default in ascending order

Cleanup and drop the indexes

DROP INDEX idx_film_length ON film;DROP INDEX idx_film_length_desc ON film;