mysql date time indexes not applying for some specific date ranges

Question

I have a table with this schema -

CREATE TABLE `ox_data_summary_device_hourly` ( `data_summary_device_hourly_id` bigint(20) NOT NULL AUTO_INCREMENT, `date_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', ... ... PRIMARY KEY (`data_summary_device_hourly_id`), KEY `ox_data_summary_device_daily_date_time` (`date_time`), KEY `ox_data_summary_device_daily_ad_country_date_time` (`ad_id`,`date_time`), KEY `ox_data_summary_device_daily_zone_id_date_time` (`zone_id`,`date_time`) ) ENGINE=InnoDB AUTO_INCREMENT=3478837682 DEFAULT CHARSET=latin1

So, the following indexes are set on a table -

 KEY `ox_data_summary_device_daily_date_time` (`date_time`), KEY `ox_data_summary_device_daily_ad_country_date_time` (`ad_id`,`date_time`), KEY `ox_data_summary_device_daily_zone_id_date_time` (`zone_id`,`date_time`)

I have this explain query on this table with filter on date_time -

explain SELECT * FROM ox_data_summary_device_hourly data_hourly WHERE data_hourly.date_time >= '2012-09-01 00:00:00' AND data_hourly.date_time < '2012-09-15 13:00:00' GROUP BY data_hourly.type ORDER BY data_hourly.type DESC

Here is the output showing that the ox_data_summary_device_daily_date_time index is not applying -

enter image description here

However, if I change the date filter in the above mentioned query to the following -

data_hourly.date_time >= '2012-09-01 00:00:00' AND data_hourly.date_time < '2012-09-14 13:00:00'

i.e. if I query for 1st Sep to 14 sep instead of 1st to 15 sep, the ox_data_summary_device_daily_date_time index applies. I further checked that if the end date of the range is anything after 15 sep, the index does not apply.

I am puzzled. I checked that there is data in the table for date range >= 15th Sep.

Any ideas?

Ross Smith II · Accepted Answer · 2012-09-17 15:42:01Z

I'm not sure why the index is not being used, but you might solve this by adding the FORCE INDEX option:

SELECT * FROM ox_data_summary_device_hourly data_hourly FORCE INDEX (ox_data_summary_device_daily_date_time) WHERE data_hourly.date_time >= '2012-09-01 00:00:00' AND data_hourly.date_time < '2012-09-15 13:00:00' GROUP BY data_hourly.type ORDER BY data_hourly.type DESC

Also, you may find using the BETWEEN operator easier to read, and it may even perform slightly better:

SELECT * FROM ox_data_summary_device_hourly data_hourly FORCE INDEX (ox_data_summary_device_daily_date_time) WHERE data_hourly.date_time BETWEEN '2012-09-01 00:00:00' AND '2012-09-15 13:00:00' - INTERVAL 1 SECOND GROUP BY data_hourly.type ORDER BY data_hourly.type DESC

Alain Collins · Accepted Answer · 2012-09-17 15:48:29Z

Sounds like the optimizer to me - it thinks there are too many rows being examined by the query to make the index useful. That explains why it's OK for a smaller range, too.

I think you can either query for smaller date ranges or use a DATE instead of a DATETIME.

Running ANALYZE or OPTIMIZE might help, too. Here's a decent overview article

Good luck.

Collectives™ on Stack Overflow

mysql date time indexes not applying for some specific date ranges

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related