Bug #74256 | index_extensions used incorrectly in select cause performance regression | ||
---|---|---|---|
Submitted: | 7 Oct 2014 22:43 | Modified: | 14 Jan 2015 8:39 |
Reporter: | Gavin Towey | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
Version: | 5.6.19, 5.6.22 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[7 Oct 2014 22:43]
Gavin Towey
[14 Jan 2015 7:47]
Valeriy Kravchuk
I think this is a 5.6-specific kind of http://bugs.mysql.com/bug.php?id=12113 (with ICP used).
[14 Jan 2015 8:39]
MySQL Verification Team
Hello Gavin Towey, Thank you for the report and test case. Thanks, Umesh
[14 Jan 2015 8:40]
MySQL Verification Team
[test]> show variables like '%version%'; +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | innodb_version | 5.6.22 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.22 | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+------------------------------+ 7 rows in set (0.00 sec) [(none)]> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed [test]> create table index_cond_test ( id bigint unsigned not null auto_increment primary key, flag int unsigned, name varchar(255), index (flag) ); Query OK, 0 rows affected (0.05 sec) [test]> insert into index_cond_test values (NULL, 0, MD5(RAND()) ), (NULL, 0, MD5(RAND())), (NULL, 0, MD5(RAND())), (NULL,0, MD5(RAND())); insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a, index_cond_test b; Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 [test]> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a, index_cond_test b; Query OK, 16 rows affected (0.03 sec) Records: 16 Duplicates: 0 Warnings: 0 [test]> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a, index_cond_test b; Query OK, 400 rows affected (0.01 sec) Records: 400 Duplicates: 0 Warnings: 0 [test]> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a; insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a; insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a; Query OK, 420 rows affected (0.02 sec) Records: 420 Duplicates: 0 Warnings: 0 [test]> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a; insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a;Query OK, 840 rows affected (0.03 sec) Records: 840 Duplicates: 0 Warnings: 0 [test]> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a; Query OK, 1680 rows affected (0.06 sec) Records: 1680 Duplicates: 0 Warnings: 0 [test]> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a; Query OK, 3360 rows affected (0.09 sec) Records: 3360 Duplicates: 0 Warnings: 0 [test]> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a; Query OK, 6720 rows affected (0.18 sec) Records: 6720 Duplicates: 0 Warnings: 0 [test]> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a; Query OK, 13440 rows affected (0.43 sec) Records: 13440 Duplicates: 0 Warnings: 0 [test]> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a; Query OK, 26880 rows affected (0.46 sec) Records: 26880 Duplicates: 0 Warnings: 0 [test]> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a; Query OK, 53760 rows affected (2.15 sec) Records: 53760 Duplicates: 0 Warnings: 0 [test]> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a; Query OK, 107520 rows affected (1.77 sec) Records: 107520 Duplicates: 0 Warnings: 0 [test]> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a; Query OK, 215040 rows affected (3.47 sec) Records: 215040 Duplicates: 0 Warnings: 0 [test]> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a; Query OK, 430080 rows affected (6.92 sec) Records: 430080 Duplicates: 0 Warnings: 0 [test]> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a limit 1000000; Query OK, 860160 rows affected (13.95 sec) Records: 860160 Duplicates: 0 Warnings: 0 [test]> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a limit 1000000; Query OK, 1000000 rows affected (19.88 sec) Records: 1000000 Duplicates: 0 Warnings: 0 [test]> select count(*) from index_cond_test; +----------+ | count(*) | +----------+ | 2720320 | +----------+ 1 row in set (15.34 sec) [test]> update index_cond_test SET flag=IF(RAND()<.01, UNIX_TIMESTAMP(NOW()-INTERVAL RAND()*400000 MINUTE), 0), name=MD5(RAND()); Query OK, 2720320 rows affected (1 min 29.59 sec) Rows matched: 2720320 Changed: 2720320 Warnings: 0 [test]> show indexes from index_cond_test; +-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | index_cond_test | 0 | PRIMARY | 1 | id | A | 2714829 | NULL | NULL | | BTREE | | | | index_cond_test | 1 | flag | 1 | flag | A | 1357414 | NULL | NULL | YES | BTREE | | | +-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec) [test]> analyze table index_cond_test; +----------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------------+---------+----------+----------+ | test.index_cond_test | analyze | status | OK | +----------------------+---------+----------+----------+ 1 row in set (0.01 sec) [test]> show indexes from index_cond_test; +-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | index_cond_test | 0 | PRIMARY | 1 | id | A | 2707185 | NULL | NULL | | BTREE | | | | index_cond_test | 1 | flag | 1 | flag | A | 52061 | NULL | NULL | YES | BTREE | | | +-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec)
[14 Jan 2015 8:40]
MySQL Verification Team
[test]> explain select id from index_cond_test WHERE flag=FALSE AND id in ('10521571','10521559','10521555','10521535') ORDER BY name, id; +----+-------------+-----------------+-------+---------------+---------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------+-------+---------------+---------+---------+------+------+-----------------------------+ | 1 | SIMPLE | index_cond_test | range | PRIMARY,flag | PRIMARY | 8 | NULL | 4 | Using where; Using filesort | +----+-------------+-----------------+-------+---------------+---------+---------+------+------+-----------------------------+ 1 row in set (0.03 sec) [test]> update index_cond_test SET flag=IF(RAND()<.2, UNIX_TIMESTAMP(NOW()-INTERVAL RAND()*400000 MINUTE), 0); Query OK, 565163 rows affected (52.55 sec) Rows matched: 2720320 Changed: 565163 Warnings: 0 [test]> analyze table index_cond_test; show indexes from index_cond_test; +----------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------------+---------+----------+----------+ | test.index_cond_test | analyze | status | OK | +----------------------+---------+----------+----------+ 1 row in set (0.01 sec) +-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | index_cond_test | 0 | PRIMARY | 1 | id | A | 2707185 | NULL | NULL | | BTREE | | | | index_cond_test | 1 | flag | 1 | flag | A | 676796 | NULL | NULL | YES | BTREE | | | +-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec) [test]> explain select id from index_cond_test WHERE flag=FALSE AND id in ('10521571','10521559','10521555','10521535') ORDER BY name, id; +----+-------------+-----------------+------+---------------+------+---------+-------+------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------+------+---------------+------+---------+-------+------+----------------------------------------------------+ | 1 | SIMPLE | index_cond_test | ref | PRIMARY,flag | flag | 5 | const | 4 | Using index condition; Using where; Using filesort | +----+-------------+-----------------+------+---------------+------+---------+-------+------+----------------------------------------------------+ 1 row in set (0.00 sec) [test]> explain select id from index_cond_test use index (flag) WHERE flag=FALSE AND id in ('10521571','10521559','10521555','10521535') ORDER BY name, id; +----+-------------+-----------------+-------+---------------+------+---------+------+------+---------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------+-------+---------------+------+---------+------+------+---------------------------------------+ | 1 | SIMPLE | index_cond_test | range | flag | flag | 13 | NULL | 4 | Using index condition; Using filesort | +----+-------------+-----------------+-------+---------------+------+---------+------+------+---------------------------------------+ 1 row in set (0.00 sec)
[14 Jan 2015 9:42]
MySQL Verification Team
// 5.6.24 mysql> show variables like '%version%'; +-------------------------+---------------------------------------------------------+ | Variable_name | Value | +-------------------------+---------------------------------------------------------+ | innodb_version | 5.6.24 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.24-enterprise-commercial-advanced | | version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) | | version_compile_machine | x86_64 | | version_compile_os | linux-glibc2.5 | +-------------------------+---------------------------------------------------------+ 7 rows in set (0.00 sec) mysql> create table index_cond_test ( id bigint unsigned not null auto_increment primary key, flag int unsigned, name varchar(255), index (flag) ); Query OK, 0 rows affected (0.01 sec) mysql> insert into index_cond_test values (NULL, 0, MD5(RAND()) ), (NULL, 0, MD5(RAND())), (NULL, 0, MD5(RAND())), (NULL,0, MD5(RAND())); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a, index_cond_test b; Query OK, 16 rows affected (0.00 sec) Records: 16 Duplicates: 0 Warnings: 0 mysql> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a, index_cond_test b; Query OK, 400 rows affected (0.00 sec) Records: 400 Duplicates: 0 Warnings: 0 mysql> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a; Query OK, 420 rows affected (0.01 sec) Records: 420 Duplicates: 0 Warnings: 0 mysql> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a; Query OK, 840 rows affected (0.00 sec) Records: 840 Duplicates: 0 Warnings: 0 mysql> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a; Query OK, 1680 rows affected (0.01 sec) Records: 1680 Duplicates: 0 Warnings: 0 mysql> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a; Query OK, 3360 rows affected (0.02 sec) Records: 3360 Duplicates: 0 Warnings: 0 mysql> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a; Query OK, 6720 rows affected (0.04 sec) Records: 6720 Duplicates: 0 Warnings: 0 mysql> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a; Query OK, 13440 rows affected (0.09 sec) Records: 13440 Duplicates: 0 Warnings: 0 mysql> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a; Query OK, 26880 rows affected (0.17 sec) Records: 26880 Duplicates: 0 Warnings: 0 mysql> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a; Query OK, 53760 rows affected (0.33 sec) Records: 53760 Duplicates: 0 Warnings: 0 mysql> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a; Query OK, 107520 rows affected (0.64 sec) Records: 107520 Duplicates: 0 Warnings: 0 mysql> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a; Query OK, 215040 rows affected (1.36 sec) Records: 215040 Duplicates: 0 Warnings: 0 mysql> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a; Query OK, 430080 rows affected (2.56 sec) Records: 430080 Duplicates: 0 Warnings: 0 mysql> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a limit 1000000; Query OK, 860160 rows affected (5.11 sec) Records: 860160 Duplicates: 0 Warnings: 0 mysql> insert into index_cond_test select NULL, 0, 0 FROM index_cond_test a limit 1000000; Query OK, 1000000 rows affected (5.81 sec) Records: 1000000 Duplicates: 0 Warnings: 0 mysql> select count(*) from index_cond_test; +----------+ | count(*) | +----------+ | 2720320 | +----------+ 1 row in set (0.53 sec) mysql> update index_cond_test SET flag=IF(RAND()<.01, UNIX_TIMESTAMP(NOW()-INTERVAL RAND()*400000 MINUTE), 0), name=MD5(RAND()); Query OK, 2720320 rows affected (47.56 sec) Rows matched: 2720320 Changed: 2720320 Warnings: 0 mysql> show indexes from index_cond_test; +-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | index_cond_test | 0 | PRIMARY | 1 | id | A | 2714829 | NULL | NULL | | BTREE | | | | index_cond_test | 1 | flag | 1 | flag | A | 1357414 | NULL | NULL | YES | BTREE | | | +-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec) mysql> analyze table index_cond_test; +----------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------------+---------+----------+----------+ | test.index_cond_test | analyze | status | OK | +----------------------+---------+----------+----------+ 1 row in set (0.01 sec) mysql> show indexes from index_cond_test; +-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | index_cond_test | 0 | PRIMARY | 1 | id | A | 2707185 | NULL | NULL | | BTREE | | | | index_cond_test | 1 | flag | 1 | flag | A | 52061 | NULL | NULL | YES | BTREE | | | +-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec)
[14 Jan 2015 9:42]
MySQL Verification Team
// 5.6.24 mysql> explain select id from index_cond_test WHERE flag=FALSE AND id in ('10521571','10521559','10521555','10521535') ORDER BY name, id; +----+-------------+-----------------+-------+---------------+---------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------+-------+---------------+---------+---------+------+------+-----------------------------+ | 1 | SIMPLE | index_cond_test | range | PRIMARY,flag | PRIMARY | 8 | NULL | 4 | Using where; Using filesort | +----+-------------+-----------------+-------+---------------+---------+---------+------+------+-----------------------------+ 1 row in set (0.00 sec) mysql> update index_cond_test SET flag=IF(RAND()<.2, UNIX_TIMESTAMP(NOW()-INTERVAL RAND()*400000 MINUTE), 0); Query OK, 565521 rows affected (19.91 sec) Rows matched: 2720320 Changed: 565521 Warnings: 0 mysql> analyze table index_cond_test; show indexes from index_cond_test; +----------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------------+---------+----------+----------+ | test.index_cond_test | analyze | status | OK | +----------------------+---------+----------+----------+ 1 row in set (0.03 sec) +-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | index_cond_test | 0 | PRIMARY | 1 | id | A | 2707185 | NULL | NULL | | BTREE | | | | index_cond_test | 1 | flag | 1 | flag | A | 676796 | NULL | NULL | YES | BTREE | | | +-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec) mysql> explain select id from index_cond_test WHERE flag=FALSE AND id in ('10521571','10521559','10521555','10521535') ORDER BY name, id; +----+-------------+-----------------+------+---------------+------+---------+-------+------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------+------+---------------+------+---------+-------+------+----------------------------------------------------+ | 1 | SIMPLE | index_cond_test | ref | PRIMARY,flag | flag | 5 | const | 4 | Using index condition; Using where; Using filesort | +----+-------------+-----------------+------+---------------+------+---------+-------+------+----------------------------------------------------+ 1 row in set (0.00 sec) mysql> explain select id from index_cond_test use index (flag) WHERE flag=FALSE AND id in ('10521571','10521559','10521555','10521535') ORDER BY name, id; +----+-------------+-----------------+-------+---------------+------+---------+------+------+---------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------+-------+---------------+------+---------+------+------+---------------------------------------+ | 1 | SIMPLE | index_cond_test | range | flag | flag | 13 | NULL | 4 | Using index condition; Using filesort | +----+-------------+-----------------+-------+---------------+------+---------+------+------+---------------------------------------+ 1 row in set (0.00 sec)
[2 Nov 2015 6:19]
Øystein Grøvlen
Posted by developer: Since MySQL 5.7.5, the given test case no longer use the bad query plan. With WL#7339 (Use improved records per key estimate interface in optimizer), InnoDB statistics are more precise, and the PRIMARY index will be selected. I think that will be the case regardless of the cardinality of the flag column.