Select day of week from date

Question

I have the following table in MySQL that records event counts of stuff happening each day

event_date     event_count 2011-05-03     21 2011-05-04     12 2011-05-05     12 

I want to be able to query this efficiently by date range AND by day of week. For example - "What is the event_count on Tuesdays in May?"

Currently the event_date field is a date type. Are there any functions in MySQL that let me query this column by day of week, or should I add another column to the table to store the day of week?

The table will hold hundreds of thousands of rows, so given a choice I'll choose the most efficient solution (as opposed to most simple).

Answer 1

Use DAYOFWEEK in your query, something like:

SELECT * FROM mytable WHERE MONTH(event_date) = 5 AND DAYOFWEEK(event_date) = 7; 

This will find all info for Saturdays in May.

To get the fastest reads store a denormalized field that is the day of the week (and whatever else you need). That way you can index columns and avoid full table scans.

Just try the above first to see if it suits your needs and if it doesn't, add some extra columns and store the data on write. Just watch out for update anomalies (make sure you update the day_of_week column if you change event_date).

Note that the denormalized fields will increase the time taken to do writes, increase calculations on write, and take up more space. Make sure you really need the benefit and can measure that it helps you.