Description:
There's no "covering index" scan for a functional index.
- Explain doesn't mention it.
- Time benchmark also shows that covering doesn't really happen.
The query is like `SELECT id FROM table where f(col)=?`, assuming the index built on `f(col)`.
I've carefully examined the docs, but there's no mention that a functional index can't be covering.
How to repeat:
This test below is a courtesy of Sveta Smirnova, any functional index demonstrates the issue.
mysql🐬> desc js;
+-------+------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| f1 | int | YES | MUL | NULL | |
+-------+------+------+-----+---------+----------------+
2 rows in set (0,01 sec)
mysql🐬> alter table js add index ni(f1);
Query OK, 0 rows affected (0,11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql🐬> explain select id from js where f1=24;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | js | NULL | ref | ni | ni | 5 | const | 12 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0,00 sec)
mysql🐬> alter table js drop index ni;
Query OK, 0 rows affected (0,02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql🐬> alter table js add index fi((f1%96));
Query OK, 0 rows affected (0,12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql🐬> explain select id from js where f1%96=12;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | js | NULL | ref | fi | fi | 5 | const | 183 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0,00 sec)
No "using index" for the "functional" explain, hence no covering.
