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:
None 
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 9:40] Hao Liu
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...
[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.