Welcome to WebmasterWorld Guest from 54.146.246.4

Forum Moderators: open

Message Too Old, No Replies

MySQL: Select records from given month, year or day

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

     

sunroof

9:48 pm on Dec 15, 2008 (gmt 0)

5+ Year Member



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

10:12 pm on Dec 15, 2008 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

10:34 pm on Dec 15, 2008 (gmt 0)

5+ Year Member



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

9:30 am on Dec 16, 2008 (gmt 0)

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



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

7:44 pm on Jan 2, 2009 (gmt 0)

5+ Year Member



What about something like this?

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

Is this the fastest?

phranque

4:45 am on Jan 3, 2009 (gmt 0)

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



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

5:18 pm on Jan 3, 2009 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

10:40 pm on Jan 3, 2009 (gmt 0)

WebmasterWorld Senior Member g1smd is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



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

rocknbil

2:50 pm on Jan 4, 2009 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



What was I thinking. Better yet:

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

phranque

2:10 am on Jan 5, 2009 (gmt 0)

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



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...