MySQL query by date with big inverval

Question

I have big table with 22 millions records. I want to execute next query:

select auto_alerts from alerts_stat  where endDate > "2012-12-01"

To improve performance I added BTREE index for endData field:

CREATE INDEX endDate_index USING BTREE ON alerts_stat(endDate)

After I start to analyze query execution plan:

When I want to get parameters from 15 to 7 days before now:

explain select alerts_sp from alerts_stat 
where endDate between CURDATE() - 15 and CURDATE() - 7;

I got next execution plan to process 2,762,088 rows.

'1', 'SIMPLE', 'browser_plugin_alerts_stat', 'range', 'endDate_index', 'endDate_index', '4', NULL, '2762088', 'Using where'

When I increase interval by one day, i received:

explain select alerts_sp from alerts_stat 
where endDate between CURDATE() - 15 and CURDATE() - 6;

EXPLAIN said MySQL plan to process all 22,923,126 rows.

'1', 'SIMPLE', 'browser_plugin_alerts_stat', 'ALL', 'endDate_index', NULL, NULL, NULL, '22932390', 'Using where'

For example select without any conditions in WHERE process 22,925,642.

May I improve execution plan? Maybe I have mistake somewhere, or is normal MySQL behaivior?

Athome

Answer 1

When result set exceeds 8-9% of all rows, MySQL does full table scan. To me, it looks like that one day you add swings MySQL in full table scan direction. You could try forcing index to see if result is better.

UPDATE:

From what I've read, MySQL query optimizer tends to choose wrong in borderline cases like this, so it could easily work better forcing an index. Otherwise, this is simple query, and I don't much more room for optimization.

Maybe creating a Covering index on these two columns and forcing its use could give best results.

Leandraleandre

Thanks. Think it is my case. So it is MySQL specific behavior. Can I improve query somehow to get for example sum('alerts_sp') for big interval? – Athome