homepage Welcome to WebmasterWorld Guest from 54.237.78.165
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
MySQL: Select records from given month, year or day
The fastest way to select all records for a given year, month or day?
sunroof

5+ Year Member



 
Msg#: 3808173 posted 9:48 pm on Dec 15, 2008 (gmt 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!

 

coopster

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



 
Msg#: 3808173 posted 10:12 pm on Dec 15, 2008 (gmt 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'

sunroof

5+ Year Member



 
Msg#: 3808173 posted 10:34 pm on Dec 15, 2008 (gmt 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.

phranque

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



 
Msg#: 3808173 posted 9:30 am on Dec 16, 2008 (gmt 0)

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

5+ Year Member



 
Msg#: 3808173 posted 7:44 pm on Jan 2, 2009 (gmt 0)

What about something like this?

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

Is this the fastest?

phranque

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



 
Msg#: 3808173 posted 4:45 am on Jan 3, 2009 (gmt 0)

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

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



 
Msg#: 3808173 posted 5:18 pm on Jan 3, 2009 (gmt 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'

g1smd

WebmasterWorld Senior Member g1smd us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 3808173 posted 10:40 pm on Jan 3, 2009 (gmt 0)

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

rocknbil

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



 
Msg#: 3808173 posted 2:50 pm on Jan 4, 2009 (gmt 0)

What was I thinking. Better yet:

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

phranque

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



 
Msg#: 3808173 posted 2:10 am on Jan 5, 2009 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved