MySQL: Select records from given month, year or day

The fastest way to select all records for a given year, month or day?

[sunroof]

Hi everyone.

I have a simple mysql table with 2 million of records with ID, Date, and few other fields.

ID (int 11) DATE (type: datetime) 
---------------------------------- 
1584266  2008-06-01 12:41:37 
1585613  2008-06-01 23:13:53 
1586086  2008-06-02 11:22:03 
1586708  2008-06-02 17:22:24 
1587740  2008-06-03 00:07:12 
1587752  2008-06-03 00:19:41 
1588008  2008-06-03 11:54:26 
1589417  2008-06-03 22:26:11 
1589519  2008-06-04 09:35:13 
etc... 

What is the fastest way to select all records for a given year, month or day?

For example:

- SELECT all records from January 2008
- SELECT all records from September 1st 2008.
- SELECT all records from 2008

I set up index on date field, but when I read all rows with these queries, it takes too much time:

- SELECT * FROM table WHERE date LIKE '2008-27-03-%'
- SELECT * FROM table WHERE date LIKE '2008-27-%'

Thanks a lot!

[coopster]

Rather than using LIKE, how about BETWEEN?

WHERE `date` BETWEEN '2008-01-01' AND '2008-01-31' 
WHERE `date` BETWEEN '2008-09-01' AND CURRENT_DATE 
WHERE `date` BETWEEN '2008-01-01' AND '2008-12-31'

[sunroof]

WHERE `date` BETWEEN '2008-01-01' AND '2008-01-31'
or
WHERE `date` BETWEEN '2008-01-01 00:00:00' AND '2008-01-31 23:59:59'
?

In this case I have to check number of days the in the specific month. But it's worth trying.

One more thing.

What is the fastest way to COUNT all records in the table?

COUNT (*) FROM ... WHERE....
or
COUNT (id) FROM ... WHERE...

Thanks again.

[phranque]

COUNT(*) on a single table without a WHERE is retrieved directly from the table information for MyISAM and MEMORY tables. This is also done for any NOT NULL expression when used with only one table.

from mysql's doc on "WHERE Clause Optimization [dev.mysql.com]"

[sunroof]

What about something like this?

... WHERE YEAR(date) = 2008
AND MONTH(date) = 12
AND DAYOFMONTH(05)

Is this the fastest?

[phranque]

no that will be slower.
that will have to process those three values for every row before it can count them.
better to use the BETWEEN syntax with an index on that date column.

[rocknbil]

In this case I have to check number of days the in the specific month.

This also works, and you only have to increment the month before the query.

where date >= '2008-01-01' and date < '2008-02-01'

[g1smd]

You'll need to add one to the year when specifying the January end-date for December data.

[rocknbil]

What was I thinking. Better yet:

where date >= '2008-01-01' and date < date_add('2008-01-01',interval 1 month);

[phranque]

date_add('2008-01-01',interval 1 month)

x 2,000,000 records!

i guess it depends on how well that query is optimized and still assuming an index on that row...