| Bug #72646 | A strange result from explain query | ||
|---|---|---|---|
| Submitted: | 14 May 2014 9:40 | Modified: | 14 May 2014 12:17 |
| Reporter: | Hao Liu | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.5 5.6, 5.6.19 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[14 May 2014 11:33]
MySQL Verification Team
Hello Hao, Thank you for the bug report. Verified as described. Thanks, Umesh
[14 May 2014 11:34]
MySQL Verification Team
// with 5.6.19
mysql> CREATE TABLE `sbtest1` (
-> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-> `k` int(10) unsigned NOT NULL DEFAULT '0',
-> `c` char(120) NOT NULL DEFAULT '',
-> `pad` char(60) NOT NULL DEFAULT '',
-> PRIMARY KEY (`id`),
-> KEY `k` (`k`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)
mysql> set @id:=0;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into `sbtest1` values (@id:=@id+1,@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000)),(@id:=@id+1,@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000)),(@id:=@id+1,@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000)),(@id:=@id+1,@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000));
Query OK, 4 rows affected, 1 warning (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 1
mysql> insert into `keyvalue`(`id`,`k`,`c`,`pad`)
-> select @id:=@id+1,@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000) from
-> `keyvalue` k1, `keyvalue` k2, `keyvalue` k3, `keyvalue` k4,`keyvalue` k5,`keyvalue` k6, `keyvalue` k7, `keyvalue` k8, `keyvalue` k9,
-> `keyvalue` k0,`keyvalue` ka, `keyvalue` kb, `keyvalue` kc, `keyvalue` kd limit 2000001;
ERROR 1146 (42S02): Table 'test.keyvalue' doesn't exist
mysql> insert into `sbtest1`(`id`,`k`,`c`,`pad`)
-> select @id:=@id+1,@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000) from
-> `sbtest1` k1, `sbtest1` k2, `sbtest1` k3, `sbtest1` k4,`sbtest1` k5,`sbtest1` k6, `sbtest1` k7, `sbtest1` k8, `sbtest1` k9,
-> `sbtest1` k0,`sbtest1` ka, `sbtest1` kb, `sbtest1` kc, `sbtest1` kd limit 2000001;
Query OK, 2000001 rows affected, 3 warnings (55.53 sec)
Records: 2000001 Duplicates: 0 Warnings: 3
mysql> optimize table sbtest1;
+--------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------------+----------+----------+-------------------------------------------------------------------+
| test.sbtest1 | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| test.sbtest1 | optimize | status | OK |
+--------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (1 min 30.57 sec)
mysql> select id from sbtest1 where id between -1 and 5;
+----+
| id |
+----+
| 1 |
| 3 |
| 5 |
+----+
3 rows in set (4.01 sec)
mysql> explain select id from sbtest1 where id between -1 and 5;
+----+-------------+---------+-------+---------------+------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+------+---------+------+---------+--------------------------+
| 1 | SIMPLE | sbtest1 | index | PRIMARY | k | 4 | NULL | 1921969 | Using where; Using index |
+----+-------------+---------+-------+---------------+------+---------+------+---------+--------------------------+
1 row in set (0.00 sec)
mysql> show create table sbtest1\G
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=4000010 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> explain select id from sbtest1 where id between 1 and 5;
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | sbtest1 | range | PRIMARY | PRIMARY | 4 | NULL | 3 | Using where; Using index |
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
mysql> select version();
+-------------------------------------------+
| version() |
+-------------------------------------------+
| 5.6.19-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)
[14 May 2014 12:17]
Hao Liu
Hi，Umesh At the same time, I don't know why the select doesn't use any index if the id begins from -1; I judge if from the rows of explain result and the execute time.
[14 May 2014 12:17]
Hao Liu
Hi，Umesh At the same time, I don't know why the select doesn't use any index if the id begins from -1; I judge if from the rows of explain result and the execute time.
[14 May 2014 12:17]
Hao Liu
Hi，Umesh At the same time, I don't know why the select doesn't use any index if the id begins from -1; I judge if from the rows of explain result and the execute time.
Description: I have a table as below (you will see that it is a table generated by sysbench): root@test 05:32:17>show create table sbtest1\G *************************** 1. row *************************** Table: sbtest1 Create Table: CREATE TABLE `sbtest1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=2000001 DEFAULT CHARSET=gbk MAX_ROWS=1000000 1 row in set (0.00 sec) The id column is 'unsigned int'. But I use a negative number to be a 'select' condition； You will see that the explain output results are strange: root@test 05:37:26>select id from sbtest1 where id between -1 and 5; +----+ | id | +----+ | 5 | | 1 | | 2 | | 4 | | 3 | +----+ 5 rows in set (0.43 sec) root@test 05:37:35>explain select id from sbtest1 where id between -1 and 5\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sbtest1 type: index possible_keys: PRIMARY key: k key_len: 4 ref: NULL rows: 2000114 Extra: Using where; Using index 1 row in set (0.00 sec) You will see that the query don't use any index from 'rows', but the key is 'k'; Then I will drop key 'k' using 'alter table'; The explain result is below: root@test 05:38:49>explain select id from sbtest1 where id between -1 and 5\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sbtest1 type: index possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 2000114 Extra: Using where; Using index 1 row in set (0.00 sec) How to repeat: As the description Suggested fix: I don't know how...