| Bug #75510 | MySQL chooses index scan over full table scan | ||
|---|---|---|---|
| Submitted: | 15 Jan 2015 0:36 | Modified: | 16 Jan 2015 9:53 |
| Reporter: | Justin Swanhart | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.6.21 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[15 Jan 2015 4:19]
Justin Swanhart
Note, that without an additional column, the example does a covering index scan, which is correct, so you need to do the following to observe the bug:
alter table a add b int default 1;
explain select a,b from a where a = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ref
possible_keys: a
key: a
key_len: 5
ref: const
rows: 549376
Extra: NULL
1 row in set (0.00 sec)
So you can see it uses ref access instead of full table scan.
Compare times:
-- MySQL does not use index on a
mysql> select a,b from a ignore index(a) where a = 1 ;
...
1048576 rows in set (1.05 sec)
-- MySQL uses index on a
mysql> select a,b from a where a = 1 ;
...
1048576 rows in set (1.93 sec)
Here is the explain for the ignore index:
mysql> explain select a,b from a ignore index(a) where a = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1098752
Extra: Using where
1 row in set (0.00 sec)
[16 Jan 2015 9:53]
MySQL Verification Team
Hello Justin Swanhart, Thank you for the report and test case. Thanks, Umesh
[16 Jan 2015 9:55]
MySQL Verification Team
// 5.6.24
+-------------------------+---------------------------------------------------------+
| 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 a(a int,key(a)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into a values (1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into a select * from a;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 32 rows affected (0.01 sec)
Records: 32 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 64 rows affected (0.00 sec)
Records: 64 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 128 rows affected (0.00 sec)
Records: 128 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 256 rows affected (0.00 sec)
Records: 256 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 512 rows affected (0.01 sec)
Records: 512 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 1024 rows affected (0.01 sec)
Records: 1024 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 2048 rows affected (0.01 sec)
Records: 2048 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 4096 rows affected (0.03 sec)
Records: 4096 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 8192 rows affected (0.05 sec)
Records: 8192 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 16384 rows affected (0.11 sec)
Records: 16384 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 32768 rows affected (0.19 sec)
Records: 32768 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 65536 rows affected (0.37 sec)
Records: 65536 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 131072 rows affected (0.72 sec)
Records: 131072 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 262144 rows affected (1.51 sec)
Records: 262144 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 524288 rows affected (2.86 sec)
Records: 524288 Duplicates: 0 Warnings: 0
mysql> insert into a values (2);
Query OK, 1 row affected (0.00 sec)
mysql> explain select a from a where a = 1 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ref
possible_keys: a
key: a
key_len: 5
ref: const
rows: 523498
Extra: Using index
1 row in set (0.00 sec)
mysql> explain select a from a where a = 1 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ref
possible_keys: a
key: a
key_len: 5
ref: const
rows: 523498
Extra: Using index
1 row in set (0.00 sec)
mysql> explain select a from a where a = 2 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ref
possible_keys: a
key: a
key_len: 5
ref: const
rows: 1
Extra: Using index
1 row in set (0.00 sec)
mysql> alter table a add b int default 1;
Query OK, 0 rows affected (4.55 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select a,b from a where a = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ref
possible_keys: a
key: a
key_len: 5
ref: const
rows: 523441
Extra: NULL
1 row in set (0.00 sec)
mysql> select a,b from a ignore index(a) where a = 1 ;
+------+------+
1048576 rows in set (0.64 sec)
mysql> select a,b from a where a = 1 ;
+------+------+
1048576 rows in set (1.49 sec)
mysql>
mysql> explain select a,b from a ignore index(a) where a = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1046883
Extra: Using where
1 row in set (0.00 sec)
[16 Jan 2015 9:57]
MySQL Verification Team
// 5.7.6
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------+
| innodb_version | 5.7.6 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.7.6-m16-enterprise-commercial-advanced |
| version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)
mysql> create table a(a int,key(a)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into a values (1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into a select * from a;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 32 rows affected (0.00 sec)
Records: 32 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 64 rows affected (0.00 sec)
Records: 64 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 128 rows affected (0.00 sec)
Records: 128 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 256 rows affected (0.00 sec)
Records: 256 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 512 rows affected (0.00 sec)
Records: 512 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 1024 rows affected (0.01 sec)
Records: 1024 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 2048 rows affected (0.02 sec)
Records: 2048 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 4096 rows affected (0.04 sec)
Records: 4096 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 8192 rows affected (0.09 sec)
Records: 8192 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 16384 rows affected (0.17 sec)
Records: 16384 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 32768 rows affected (0.32 sec)
Records: 32768 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 65536 rows affected (0.62 sec)
Records: 65536 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 131072 rows affected (1.26 sec)
Records: 131072 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 262144 rows affected (2.61 sec)
Records: 262144 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 524288 rows affected (5.22 sec)
Records: 524288 Duplicates: 0 Warnings: 0
mysql> insert into a values (2);
Query OK, 1 row affected (0.00 sec)
mysql> explain select a from a where a = 1 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: ref
possible_keys: a
key: a
key_len: 5
ref: const
rows: 523673
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
mysql> explain select a from a where a = 2 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: ref
possible_keys: a
key: a
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
mysql> alter table a add b int default 1;
Query OK, 0 rows affected (1.88 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select a,b from a where a = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: ref
possible_keys: a
key: a
key_len: 5
ref: const
rows: 523446
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
mysql> select a,b from a ignore index(a) where a = 1 ;
+------+------+
1048576 rows in set (0.64 sec)
mysql> select a,b from a where a = 1 ;
+------+------+
1048576 rows in set (1.51 sec)
mysql> explain select a,b from a ignore index(a) where a = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1046892
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
Description: The optimizer is supposed to choose a full table scan if the index is extremely skewed. It doesn't appear to be doing that though, and uses index access instead. How to repeat: create table a(a int,key(a)) engine=innodb; insert into a values (1); insert into a select * from a; -- repeat until you insert 500K+ rows insert into a values (2); mysql> explain select a from a where a = 1 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: a type: ref possible_keys: a key: a key_len: 5 ref: const rows: 549376 Extra: Using index 1 row in set (0.00 sec) mysql> explain select a from a where a = 2 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: a type: ref possible_keys: a key: a key_len: 5 ref: const rows: 1 Extra: Using index 1 row in set (0.00 sec) Why does the database choose ref access for the first query (a=1)? I tried adding another column, suspecting covering index might be to blame, but that didn't change the plan, except to remove 'using index'. Suggested fix: unknown