Bug #31648 full table scans being done when range should be used

• Submitted: 16 Oct 2007 19:01
• Modified: 30 Aug 2012 11:22
• Reporter: Bruce Hard
• Status: Can't repeat
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 5.1.22, 5.0.50
• OS: Any
• Assigned to: Jørgen Løland
• CPU Architecture: Any
• Tags: Optimizer, range, table scan

[16 Oct 2007 19:01] Bruce Hard

Description:
When only a small range scan needs to be done (say 1/6 the table) the entire
table is scanned.

How to repeat:
see attached test.sql file

mysql test < test.sql

select count(*) from idxtest;
+----------+
| count(*) |
+----------+
|     1280 | 
+----------+

explain select val2 from idxtest where val1 > 722 and val1 < 1000;
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | idxtest | ALL  | val1          | NULL | NULL    | NULL | 1280 | Using where | 
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

explain select val2 from idxtest where val1 > 723 and val1 < 1000;
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | idxtest | range | val1          | val1 | 5       | NULL |  220 | Using where | 
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+

explain select val2 from idxtest force index (val1) where val1 > 722 and val1 < 1000;
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | idxtest | range | val1          | val1 | 5       | NULL |  222 | Using where | 
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+

explain select val2 from idxtest force index (val1) where val1 > 723 and val1 < 1000;
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | idxtest | range | val1          | val1 | 5       | NULL |  220 | Using where | 
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+

Suggested fix:
I thought we would do a range scan unless 50% of the rows would be scanned?

[16 Oct 2007 19:02] Matthew Lord

file to create and populate the idxtest table

Attachment: test.sql (application/octet-stream, text), 40.18 KiB.

[17 Nov 2007 0:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

[30 Aug 2012 11:21] Jørgen Løland

Using 5.1/5.5/5.6/trunk pulled and compiled today I get range access for all
plans:

select count(*) from idxtest;
count(*)
1280

explain select val2 from idxtest where val1 > 722 and val1 < 1000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	idxtest	range	val1	val1	5	NULL	177	Using index condition

explain select val2 from idxtest force index (val1) where val1 > 722 and val1
< 1000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	idxtest	range	val1	val1	5	NULL	177	Using index condition

explain select val2 from idxtest where val1 > 723 and val1 < 1000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	idxtest	range	val1	val1	5	NULL	176	Using index condition

explain select val2 from idxtest force index (val1) where val1 > 723 and val1
< 1000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	idxtest	range	val1	val1	5	NULL	176	Using index condition

[30 Aug 2012 11:22] Jørgen Løland

Closed because I cannot repeat.