Optimizing date query on large table.

[Sames A]

Hi

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.

SELECT * FROM MyTable WHERE MONTH(mDate) = 3;

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?

Regards

Simon