Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL query by date with big inverval

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?

like image 371
Taky Avatar asked Dec 21 '12 13:12

Taky


People also ask

How do I select a date range in MySQL?

If you need to select rows from a MySQL database' table in a date range, you need to use a command like this: SELECT * FROM table WHERE date_column >= '2014-01-01' AND date_column <= '2015-01-01';

How do I pass a date range in SQL query?

SELECT * FROM PERSONAL WHERE BIRTH_DATE_TIME BETWEEN '2000-01-01 00:00:00' AND '2002-09-18 12:00:00';

Can you order by date in MySQL?

Use the ORDER BY keyword and the name of the column by which you want to sort. This way, you'll sort the data in ascending order by this column. You could also use the ASC keyword to make it clear that the order is ascending (the earliest date is shown first, the latest date is shown last, etc.).

Does datediff working in MySQL?

DATEDIFF() function in MySQL is used to return the number of days between two specified date values. Parameter: This function accepts two parameters as given below: date1: First specified date. date2: Second specified date.


1 Answers

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.

like image 151
Zagor23 Avatar answered Sep 29 '22 21:09

Zagor23