MySQL Invisible Indexes and Descending Indexes (8.0)

MySQL 8.0 added two index features that make index management safer and more precise.

Invisible indexes: test before you drop

Dropping an index you think is unused is risky. If you were wrong, queries become slow, and re-creating a big index takes a long time. An invisible index is the safe way to test.

An invisible index still exists and is maintained, but the optimizer ignores it. If nothing gets slower, you can drop it. If something does, you make it visible again, instantly.

query.sql

SQL

SET SESSION cte_max_recursion_depth = 100000;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(60) NOT NULL,
    city VARCHAR(20) NOT NULL,
    KEY idx_city (city)
);
INSERT INTO users (email, city)
WITH RECURSIVE n AS (SELECT 1 AS i UNION ALL SELECT i + 1 FROM n WHERE i < 20000)
SELECT CONCAT('user', i, '@example.com'), CONCAT('City', MOD(i, 300)) FROM n;
ANALYZE TABLE users;

EXPLAIN FORMAT=TRADITIONAL SELECT id FROM users WHERE city = 'City42';

Output

OUTPUT

+-------------+---------+----------+----------+
| Table       | Op      | Msg_type | Msg_text |
+-------------+---------+----------+----------+
| learn.users | analyze | status   | OK       |
+-------------+---------+----------+----------+
1 row in set

+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ref  | idx_city      | idx_city | 82      | const |   67 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set

The plan uses idx_city. Now hide it:

query.sql

SQL

1
2

ALTER TABLE users ALTER INDEX idx_city INVISIBLE;
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM users WHERE city = 'City42';

Output

OUTPUT

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 20272 |     0.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set

Now the same query scans the whole table (type: ALL, key: NULL). That is what dropping the index would do. Watch the real system for a while (the slow query log, the query times). Then decide:

query.sql

SQL

1
2

ALTER TABLE users ALTER INDEX idx_city VISIBLE;
SELECT index_name, is_visible FROM information_schema.statistics WHERE table_schema = 'learn' AND table_name = 'users' ORDER BY index_name;

Output

OUTPUT

+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| idx_city   | YES        |
| PRIMARY    | YES        |
+------------+------------+
2 rows in set

Switching back is instant, because the index was kept up to date all the time.

You can also create an index as invisible, to test a new index on production without any query using it until you enable it (or until you test it in your own session):

query.sql

SQL

CREATE INDEX idx_email ON users (email) INVISIBLE;

To test a new invisible index in one session only, allow the optimizer to see invisible indexes there:

query.sql

SQL

1
2
3

SET SESSION optimizer_switch = 'use_invisible_indexes=on';
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM users WHERE email = 'user77@example.com';
SET SESSION optimizer_switch = 'use_invisible_indexes=off';

Output

OUTPUT

+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ref  | idx_email     | idx_email | 242     | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
1 row in set

Only your session sees the new index, so you can try the plan with no risk to the rest of the system.

Limit: the primary key cannot be invisible.

Descending indexes

Before MySQL 8.0, you could write DESC in an index, but it was ignored: the index was always ascending. MySQL 8 stores DESC columns in descending order, which matters for mixed sort directions.

query.sql

SQL

CREATE TABLE scores (id INT AUTO_INCREMENT PRIMARY KEY, player VARCHAR(10) NOT NULL, points INT NOT NULL, achieved_on DATE NOT NULL);
INSERT INTO scores (player, points, achieved_on) WITH RECURSIVE n AS (SELECT 1 AS i UNION ALL SELECT i + 1 FROM n WHERE i < 5000) SELECT CONCAT('p', MOD(i, 100)), MOD(i * 17, 1000), '2026-01-01' + INTERVAL MOD(i, 300) DAY FROM n;
ANALYZE TABLE scores;

CREATE INDEX idx_points_date_asc ON scores (points, achieved_on);
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM scores ORDER BY points DESC, achieved_on ASC LIMIT 5;

Output

OUTPUT

+--------------+---------+----------+----------+
| Table        | Op      | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| learn.scores | analyze | status   | OK       |
+--------------+---------+----------+----------+
1 row in set

+----+-------------+--------+------------+-------+---------------+---------------------+---------+------+------+----------+-----------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key                 | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+--------+------------+-------+---------------+---------------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | scores | NULL       | index | NULL          | idx_points_date_asc | 7       | NULL | 5000 |   100.00 | Using index; Using filesort |
+----+-------------+--------+------------+-------+---------------+---------------------+---------+------+------+----------+-----------------------------+
1 row in set

The query sorts points descending and achieved_on ascending. An index with both ascending can serve a pure ASC, ASC or a pure DESC, DESC query (read backwards), but not a mix, so Using filesort appears. An index with the same mix does serve it:

query.sql

SQL

1
2

CREATE INDEX idx_points_desc_date_asc ON scores (points DESC, achieved_on ASC);
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM scores ORDER BY points DESC, achieved_on ASC LIMIT 5;

Output

OUTPUT

+----+-------------+--------+------------+-------+---------------+--------------------------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key                      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+--------------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | scores | NULL       | index | NULL          | idx_points_desc_date_asc | 7       | NULL |    5 |   100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+--------------------------+---------+------+------+----------+-------------+
1 row in set

Now the filesort is gone. Use a descending index when a frequent query sorts different columns in different directions, such as “highest score first, then earliest date”.

When to use which

Feature	Use it for
Invisible index	Testing whether an index can be dropped, or trying a new index safely
Descending index	A query with mixed sort directions (`ORDER BY a DESC, b ASC`)

lightbulb

A safe cleanup routine

1. Find a candidate unused index. 2. Make it invisible. 3. Wait one full business cycle (a month-end if you have reports). 4. If no complaints and no slow queries, drop it.