Welcome to WebmasterWorld Guest from 54.167.86.211

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?

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

Junior Member

10+ Year Member

joined:Nov 8, 2005
posts: 83
votes: 0


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!

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

Administrator

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

joined:July 31, 2003
posts:12533
votes: 0


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'
10:34 pm on Dec 15, 2008 (gmt 0)

Junior Member

10+ Year Member

joined:Nov 8, 2005
posts:83
votes: 0


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.

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

Administrator

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

joined:Aug 10, 2004
posts:10544
votes: 8


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]"
7:44 pm on Jan 2, 2009 (gmt 0)

Junior Member

10+ Year Member

joined:Nov 8, 2005
posts:83
votes: 0


What about something like this?

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

Is this the fastest?

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

Administrator

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

joined:Aug 10, 2004
posts:10544
votes: 8


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.
5:18 pm on Jan 3, 2009 (gmt 0)

Senior Member

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

joined:Nov 28, 2004
posts:7999
votes: 0


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'

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

Senior Member

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

joined:July 3, 2002
posts:18903
votes: 0


You'll need to add one to the year when specifying the January end-date for December data.
2:50 pm on Jan 4, 2009 (gmt 0)

Senior Member

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

joined:Nov 28, 2004
posts:7999
votes: 0


What was I thinking. Better yet:

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

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

Administrator

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

joined:Aug 10, 2004
posts:10544
votes: 8


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