Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Cannot Reproduce
-
10.0.7
-
None
-
None
-
None
Description
|
CREATE TABLE `sbtest` (
|
`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=10000002 DEFAULT CHARSET=latin1;
|
|
|
select `id`,`k` from `bsbackend8`.`sbtest` where id > 2 and id <1000 ;
|
|
|
| Handler_read_next | 946268 |
|
|
|
explain select `id`,`k` from `sbtest` where id > 2 and id <1000 ;
|
+------+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | sbtest | index | PRIMARY | k | 4 | NULL | 1 | Using where; Using index |
|
+------+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+
|
|
|
select count(*) from sbtest;
|
+----------+
|
| count(*) |
|
+----------+
|
| 946268 |
|
+----------+
|
I get some additional info the raison of the full index scan is because the start value of the range does not exists in the table
|
select min(id) from sbtest;
|
+---------+
|
| min(id) |
|
+---------+
|
| 6 |
|
+---------+
|
1 row in set (0,00 sec)
|
|
|
mysql> explain select `id`,`k` from `sbtest` where id between 6 and 1000 ;
|
+------+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | sbtest | index | PRIMARY | k | 4 | NULL | 1 | Using where; Using index |
|
+------+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+
|
1 row in set (0,00 sec)
|
|
|
mysql> explain select `id`,`k` from `sbtest` where id between 2 and 1000 ;
|
+------+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | sbtest | index | PRIMARY | k | 4 | NULL | 1 | Using where; Using index |
|
+------+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+
|
1 row in set (0,00 sec)
|
|
|
explain select `id`,`k` from `sbtest` where id=2 ;
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
|
|
explain select `id`,`k` from `sbtest` where id in (2,6) ;
|
+------+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | sbtest | index | PRIMARY | k | 4 | NULL | 1 | Using where; Using index |
|
+------+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+
|
1 row in set (0,00 sec)
|