MySQL Forums
Forum List  »  General

Optimizing date query on large table.
Posted by: Sames A
Date: August 11, 2010 09:49AM


This is a hypothetical question really, apologies if this has been asked here before, I cant find anything similar.

Lets say I have a massive table (500 million records), with a date field called 'mDate' yyyy-mm-dd .
The records have date entries spanning 100's of years. The dates are evenly distributed over this period.

If I want to find all the records where the month is March? I could do something like.


My understanding is that the function MONTH() would run on every record in the table?

Am I better in this instance creating a field to hold the month value 'mMonth' as an integer and placing an index on this field?

Then running the query.

SELECT * FROM MyTable WHERE mMonth = 3;

Lets say i want to grab all records for the month of March 1795

Would the third query below perform better than the top two?

SELECT * FROM MyTable WHERE MONTH(mDate) = 3 AND YEAR(mDate) = 1795;
SELECT * FROM MyTable WHERE mDate BETWEEN '1795-03-01' AND '1795-03-31'
SELECT * FROM MyTable WHERE mMonth = 3 AND mYear = 1795;

mYear feild would be indexed as well.

Is this a case where its good to store parts of dates as ints as well?



Options: ReplyQuote

Written By
Optimizing date query on large table.
August 11, 2010 09:49AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.