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

• Submitted: 16 Oct 2007 21:01
• Modified: 17 Nov 2007 15:37
• Reporter: Bruce Hard
• Status: Verified
• Category: Server: Optimizer
• Severity: S2 (Serious)
• Version: 5.1.22, 5.0.50
• OS: Any
• Tags: range, table scan, Optimizer
• Triage: Triaged: D3 (Medium) / R4 (High) / E4 (High)

[16 Oct 2007 21: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 21:02] Matthew Lord

file to create and populate the idxtest table

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

[17 Nov 2007 1: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".