homepage Welcome to WebmasterWorld Guest from 50.19.169.37
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




msg:3808175
 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




msg:3808186
 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




msg:3808209
 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




msg:3808449
 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




msg:3818028
 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




msg:3818291
 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




msg:3818545
 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




msg:3818666
 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




msg:3818875
 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




msg:3819167
 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