Bug #37240 Is this a bug here?

• Submitted: 6 Jun 2008 9:33
• Modified: 10 Jun 2008 2:37
• Reporter: liu yu
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S5 (Performance)
• Version: 5.0.38
• OS: Linux (ubuntu 7.0.4)
• CPU Architecture: Any

[6 Jun 2008 9:33] liu yu

Description:
I create a simple table like 

create table tt(id int auto_incrment,
name varchar(10),
primary key(id)
);

When executed sql query like "select * from tt  where id between 2 and 10", primary index can not be used.
And I got the following explain result:
mysql> explain select * from tt  where id between 2 and 11 ;
+----+-------------+---------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table         | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tt | ALL  | PRIMARY       | NULL | NULL    | NULL |   20 | Using where | 
+----+-------------+---------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from tt  where id between 3 and 11;
+----+-------------+---------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table         | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tt | ALL  | PRIMARY       | NULL | NULL    | NULL |   20 | Using where | 
+----+-------------+---------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from tt  where id between 4 and 11 ;
+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table         | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | tt | range | PRIMARY       | PRIMARY | 4       | NULL |    7 | Using where | 
+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

It looks like index could be used when query result in a small range.

Is this a bug? How can I do correctly

How to repeat:
create table tt(id int auto_incrment,
name varchar(10),
primary key(id)
);
explain select * from tt  where id between 2 and 11 ;
explain select * from tt  where id between 3 and 11;
explain select * from tt  where id between 4 and 11 ;

[6 Jun 2008 19:03] Valeriy Kravchuk

Thank you for a problem report. Please, send the results of:

SHOW TABLE STATUS like tt\G

for your tt table.

[9 Jun 2008 6:15] liu yu

SHOW TABLE STATUS like 'tt'
+---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name          | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| tt | MyISAM |      10 | Dynamic    |   20 |            389 |        7796 | 281474976710655 |         2048 |         0 |             21 | 2008-05-21 01:36:31 | 2008-05-21 01:36:34 | NULL       | utf8_general_ci |     NULL |                |         | 
+---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

[9 Jun 2008 19:13] Valeriy Kravchuk

This is not a bug. Selecting 10 rows out of 20 total for a table <8K in size using ALL may be even more efficient than reading them by index.

[10 Jun 2008 2:37] liu yu

Got it.Thanks.