Bug #44549 optimizer incorrectly chooses table scan and filesort over index
Submitted: 29 Apr 2009 17:47
Reporter: Matthew Lord Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.80, 5.1.34 OS:Any
Assigned to: CPU Architecture:Any
Tags: INDEX, Optimizer, table scan

[29 Apr 2009 17:47] Matthew Lord
Description:
Using the following table:

CREATE TABLE `t1` (
  `id` varchar(36) CHARACTER SET utf8 NOT NULL,
  `ts1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ts2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  KEY `ts1` (`ts1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 

And following queries:

mysql> explain select * from t1 where ts1 between '2009-12-10' and '2010-01-03' order by ts1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: ts1
          key: ts1
      key_len: 4
          ref: NULL
         rows: 169
        Extra: Using where
1 row in set (0.00 sec)

mysql> explain select * from t1 where ts1 between '2009-12-10' and '2010-01-04' order by ts1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: ts1
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 971
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

mysql> explain select * from t1 force index(ts1) where ts1 between '2009-12-10' and '2010-01-04' order by ts1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: ts1
          key: ts1
      key_len: 4
          ref: NULL
         rows: 179
        Extra: Using where
1 row in set (0.00 sec)

---------------

It appears that the additional 10 rows that need to be examined
are enough to prefer the table scan which also incurs the need
for a filesort.  Given that ~ 20% of the rows need to be examined
and using the index alleviates the need for a filesort, the 
optimizer choice seems incorrect.

How to repeat:
Please see the attached t1.sql file along with the Description.

Suggested fix:
It looks like we may need to tweak the optimizer's cost calculation a bit in this case-unless this can be adequately explained.
[30 Apr 2009 16:47] Sveta Smirnova
Tested for regression: bug exists in older versions as well.
[25 Sep 2009 16:56] Mauro Leggieri
Hi... I posted a similar problem using 5.1.39 in Vista SP2

http://forums.mysql.com/read.php?132,283126,283126

Index is not being used anymore depending the where clause range.

Severity is marked as S3 but performance drops down a lot and make some queries unusable in large databases.

Best regards,

Mauro H. Leggieri