MySQL 8.0 Index Enhancements: Function Indexes, Index Skip Scan, Descending Indexes, and Invisible Indexes

MySQL 8.0 introduces several advanced indexing capabilities that help solve common performance problems caused by traditional indexes.

Function Indexes

Before MySQL 8.0, applying functions or arithmetic to indexed columns prevented the optimizer from using the index. By defining a functional index on a virtual column, the same query can leverage the index.

root@employees 14:09:  show index from employees;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees |          0 | PRIMARY  |            1 | emp_no      | A         |      299232 |     NULL | NULL   |      | BTREE      |         |               |
| employees |          1 | inx_date |            1 | birth_date  | A         |        4739 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

Creating a functional index on month(birth_date) allows the query where month(birth_date)=9 to use the index:

root@employees 14:35:  alter table employees add index idx_birth_date((month(birth_date)));
Query OK, 0 rows affected (0.67 sec)

root@employees 14:36:  explain select * from employees where month(birth_date)=9;
+----+-------------+-----------+------------+------+----------------+----------------+---------+-------+-------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+----------------+----------------+---------+-------+-------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref | idx_birth_date | idx_birth_date | 5       | const | 47370 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+----------------+----------------+---------+-------+-------+----------+-------+

Note that the function syntax must match the definition exactly; otherwise the optimizer cannot recognize the index.

Index Skip Scan

When a composite index’s leftmost column is not used in a query, MySQL cannot apply the index. MySQL 8.0 adds Index Skip Scan to handle such cases by scanning each distinct value of the leftmost column and union‑ing the results.

root@employees 15:16:  select * from t1;
+------+--------+
| id   | score  |
+------+--------+
|    0 |   100  |
|    0 |   200  |
|    0 |   300  |
|    0 |   400  |
|    0 |   500  |
|    0 |   600  |
|    0 |   700  |
|    0 |   800  |
|    1 |   900  |
|    1 |  1000  |
|    1 |  1100  |
|    1 |  1200  |
|    1 |  1300  |
|    1 |  1400  |
|    1 |  1500  |
|    1 |  1600  |
|    2 |  1700  |
|    2 |  1800  |
|    2 |  1900  |
+------+--------+

root@employees 15:16:  select * from t1 where score>500;

The optimizer rewrites the query into a series of UNIONs, each filtering on a distinct leftmost value, which is effective when that column has low cardinality (e.g., gender or status).

Descending Indexes

Prior to MySQL 8.0, indexes were only ascending, causing ORDER BY … DESC queries to require a filesort. MySQL 8.0 allows explicit descending indexes, eliminating the extra sorting step.

root@employees 15:43:  create index idx_salary on salaries(salary desc);
Query OK, 0 rows affected (7.39 sec)

root@employees 15:47:  explain select salary from salaries group by salary order by salary desc;
+----+-------------+----------+------------+-------+---------------+------------+---------+------+-------+----------+--------------------------+
| id | select_type | table    | partitions | type | possible_keys | key        | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+------------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | salaries | NULL       | range | idx_salary    | idx_salary | 4       | NULL | 72950|   100.00 | Using index for group-by |
+----+-------------+----------+------------+-------+---------------+------------+---------+------+-------+----------+--------------------------+

Invisible Indexes

MySQL 8.0 also supports invisible indexes, which are ignored by the optimizer unless the session explicitly enables them. This is useful for testing the impact of an index without dropping it.

root@employees 15:47:  create index idx_emp on salaries(emp_no) invisible;
Query OK, 0 rows affected (4.12 sec)

root@employees 15:58:  explain select * from salaries force index(idx_emp) where emp_no=10001;
ERROR 1176 (42000): Key 'idx_emp' doesn't exist in table 'salaries'

set @@optimizer_switch='use_invisible_indexes=on';

After enabling use_invisible_indexes , the previously invisible index can be forced or used by the optimizer.