Bug #82960 | Removing an invisible index breaks explain | ||
---|---|---|---|
Submitted: | 12 Sep 2016 19:29 | Modified: | 4 Oct 2016 15:48 |
Reporter: | Daniël van Eeden (OCA) | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
Version: | 8.0.0 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | INDEX, invisible |
[12 Sep 2016 19:29]
Daniël van Eeden
[13 Sep 2016 13:58]
MySQL Verification Team
Hello Daniël, Thank you for the report. Thanks, Umesh
[13 Sep 2016 13:59]
MySQL Verification Team
-- mysql> use test Database changed mysql> CREATE TABLE t1 ( -> i INT, -> j INT, -> k INT, -> INDEX i_idx (i) INVISIBLE -> ) ENGINE = InnoDB; Query OK, 0 rows affected (0.00 sec) mysql> CREATE INDEX j_idx ON t1 (j) INVISIBLE; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> explain select * from t1 force index(i_idx) where j='"$[0]"'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> alter table t1 drop index i_idx; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from t1 force index(i_idx) where j='"$[0]"'; ERROR 1176 (42000): Key 'i_idx' doesn't exist in table 't1' mysql> SELECT INDEX_NAME, IS_VISIBLE -> FROM INFORMATION_SCHEMA.STATISTICS -> WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1'; +------------+------------+ | INDEX_NAME | IS_VISIBLE | +------------+------------+ | j_idx | NO | | k_idx | NO | +------------+------------+ 2 rows in set (0.00 sec)
[4 Oct 2016 15:48]
Paul DuBois
Posted by developer: Noted in 8.0.1. Index hints applied to invisible indexes produced no error.