Description:
When comparing DATEs with DATETIMEs, MySQL 5.0.42 does not appear to use indexes properly. This results in extremely poor performance when compared with previous versions such as 5.0.30.
This is possibly related to the bug fix for bug #27590, where date/datetime comparisons did not work properly. Now they work, but just perform poorly.
How to repeat:
Create a table with some DATE values:
CREATE TABLE `test1` (
`id` int(11) NOT NULL auto_increment,
`dateval` date default NULL,
PRIMARY KEY (`id`),
KEY `dateval` (`dateval`)
) ENGINE=MyISAM AUTO_INCREMENT=173 DEFAULT CHARSET=latin1;
INSERT INTO `test1` VALUES (1,'2007-01-01'),(2,'2007-01-02'),(3,'2007-01-03'),(4,'2007-01-04'),
(5,'2007-01-05'),(6,'2007-01-06'),(7,'2007-01-07'),(8,'2007-01-08'),
(9,'2007-01-09'),(10,'2007-01-10'),(11,'2007-01-11');
Run EXPLAINs on a simple select against a DATE range, then against a select for the corresponding DATETIME range:
Date:
mysql> explain select * from test1 where dateval >= '2007-01-01' and dateval <= '2007-01-02';
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | test1 | range | dateval | dateval | 4 | NULL | 2 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
DateTime:
mysql> explain select * from test1 where dateval >= '2007-01-01 00:00:00' and dateval <= '2007-01-02 23:59:59';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test1 | ALL | dateval | NULL | NULL | NULL | 11 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
In the second example you can see that it is doing a full scan, and not using the index. For large data sets this is very slow.
If I try this on a 5.0.30 server, I see consistent and correct results in both cases.
Suggested fix:
DateTime to Date conversions should use indexes when possible..
