MySQL query by date with large inverval

Question

I have a large table with 22 million entries. I want to execute the following query:

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

To improve performance, I added a BTREE index for the endData field:

 CREATE INDEX endDate_index USING BTREE ON alerts_stat(endDate) 

After I begin to analyze the query execution plan:

When I want to get the parameters 15 to 7 days before this:

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

I got the following execution plan for processing 277688 lines.

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

When I increase the interval by one day, I got:

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

EXPLAIN said MySQL plans 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 unconditionally in the WHERE 22 925 642 process.

Can the implementation plan be improved? Maybe I'm wrong somewhere or the normal MySQL behaivior?

Answer 1

When the result set exceeds 8-9% of all rows, MySQL performs a full table scan. It seems to me that one day you add MySQL swings in the full direction of the table scan. You can try to force the index to see if the result is better.

UPDATE:

From what I read, the MySQL query optimizer, as a rule, chooses incorrectly in borderline cases, so that it can work easily, improving index forcing. Otherwise, this is a simple request, and I have no more room for optimization.

It is possible to create a Coverage Index in these two columns and enforcing it can give the best results.