| Bug #80856 | ICP Inconsistency when range is Involved | ||
|---|---|---|---|
| Submitted: | 25 Mar 2016 3:54 | Modified: | 31 Mar 2016 7:01 |
| Reporter: | Jervin R | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.6.29 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[25 Mar 2016 3:54]
Jervin R
[28 Mar 2016 8:32]
MySQL Verification Team
Hello Jervin, Thank you for the report and test case. Thanks, Umesh
[29 Mar 2016 8:07]
Øystein Grøvlen
Posted by developer: Seems to be fixed in MySQL 5.7: mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.9 | +-----------+ 1 row in set (0,00 sec) mysql> create table t (id int unsigned not null auto_increment primary key, x int not null default 0, key (x, id)) engine=innodb; Query OK, 0 rows affected (0,01 sec) mysql> insert into t (x) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (10), (7), (13), (15), (16); Query OK, 15 rows affected (0,00 sec) Records: 15 Duplicates: 0 Warnings: 0 mysql> insert into t (x) select x from t; Query OK, 15 rows affected (0,01 sec) Records: 15 Duplicates: 0 Warnings: 0 mysql> insert into t (x) select x from t; Query OK, 30 rows affected (0,00 sec) Records: 30 Duplicates: 0 Warnings: 0 mysql> insert into t (x) select x from t; Query OK, 60 rows affected (0,00 sec) Records: 60 Duplicates: 0 Warnings: 0 mysql> insert into t (x) select x from t; Query OK, 120 rows affected (0,00 sec) Records: 120 Duplicates: 0 Warnings: 0 mysql> insert into t (x) select x from t; Query OK, 240 rows affected (0,00 sec) Records: 240 Duplicates: 0 Warnings: 0 mysql> insert into t (x) select x from t; Query OK, 480 rows affected (0,01 sec) Records: 480 Duplicates: 0 Warnings: 0 mysql> insert into t (x) select x from t; Query OK, 960 rows affected (0,02 sec) Records: 960 Duplicates: 0 Warnings: 0 mysql> insert into t (x) select x from t; Query OK, 1920 rows affected (0,05 sec) Records: 1920 Duplicates: 0 Warnings: 0 mysql> insert into t (x) select x from t; Query OK, 3840 rows affected (0,11 sec) Records: 3840 Duplicates: 0 Warnings: 0 mysql> insert into t (x) select x from t; Query OK, 7680 rows affected (0,19 sec) Records: 7680 Duplicates: 0 Warnings: 0 mysql> insert into t (x) select x from t; Query OK, 15360 rows affected (0,31 sec) Records: 15360 Duplicates: 0 Warnings: 0 mysql> insert into t (x) select x from t; Query OK, 30720 rows affected (0,56 sec) Records: 30720 Duplicates: 0 Warnings: 0 mysql> insert into t (x) select x from t; Query OK, 61440 rows affected (0,88 sec) Records: 61440 Duplicates: 0 Warnings: 0 mysql> insert into t (x) select x from t; Query OK, 122880 rows affected (1,99 sec) Records: 122880 Duplicates: 0 Warnings: 0 mysql> insert into t (x) select x from t; Query OK, 245760 rows affected (3,75 sec) Records: 245760 Duplicates: 0 Warnings: 0 mysql> insert into t (x) select x from t; Query OK, 491520 rows affected (7,22 sec) Records: 491520 Duplicates: 0 Warnings: 0 mysql> analyze table t; +--------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------+---------+----------+----------+ | test.t | analyze | status | OK | +--------+---------+----------+----------+ 1 row in set (0,01 sec) mysql> explain select * from t where x = 13 and id between 245760 and 245762; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t | NULL | range | PRIMARY,x | x | 8 | NULL | 1 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0,01 sec)
[29 Mar 2016 8:36]
Jervin R
Any chance this will be backported to 5.6? It is a long way to go before it becomes EOL.
[29 Mar 2016 13:33]
Øystein Grøvlen
I tried 5.6.29, and after running ANALYZE TABLE, I got a different plan: mysql> select version(); +-----------+ | version() | +-----------+ | 5.6.29 | +-----------+ 1 row in set (0,00 sec) mysql> explain select * from t where x = 13 and id between 245760 and 245762; +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | 1 | SIMPLE | t | ref | PRIMARY,x | x | 4 | const | 1 | Using where; Using index | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ 1 row in set (0,00 sec) mysql> analyze table t; +--------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------+---------+----------+----------+ | dbt3.t | analyze | status | OK | +--------+---------+----------+----------+ 1 row in set (0,00 sec) mysql> explain select * from t where x = 13 and id between 245760 and 245762; +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | t | range | PRIMARY,x | PRIMARY | 4 | NULL | 3 | Using where | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0,00 sec) While the above plan is not optimal (it will read 3 rows instead of 1), it is certainly much better than the plan before ANALYZE was done. If the range becomes larger, the optimizer will use the full index x as in 5.7: mysql> explain select * from t where x = 13 and id between 245760 and 245765; +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | t | range | PRIMARY,x | x | 8 | NULL | 1 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set (0,00 sec) To me, this seems like a rather limited problem. Note also that this issue has nothing to do with Index Condition Pushdown (ICP). ICP is about pushing down conditions to be evaluated on the index before look-ups into the table. This does not make much sense in the context of covering index scans. The issue here is whether ref access or range access should be used. Before ANALYZE is run, ref access is chosen because statistics is not up-to-date. (#rows to be read are underestimated). After ANALYZE, statistics is correct and ref access is not used. However, due to some error in cost estimation, the wrong index is picked.
[30 Mar 2016 14:20]
Øystein Grøvlen
Posted by developer: This was fixed in MySQL 5.7.0 by fix for Bug#14095506
[31 Mar 2016 7:01]
Øystein Grøvlen
Fixed in 5.7.0 by Internal Bug#14095506
