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.