mysql - Select day of week from date -

i have following table in mysql 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 able query efficiently date range , day of week. example - "what event_count on tuesdays in may?"

currently event_date field date type. there functions in mysql let me query column day of week, or should add column table store day of week?

the table hold hundreds of thousands of rows, given choice i'll choose efficient solution (as opposed simple).

use dayofweek in query, like:

select * mytable month(event_date) = 5 , dayofweek(event_date) = 7; 

this find info saturdays in may.

to fastest reads store denormalized field day of week (and whatever else need). way can index columns , avoid full table scans.

just try above first see if suits needs , if doesn't, add columns , store data on write. watch out update anomalies (make sure update day_of_week column if change event_date).

note denormalized fields increase time taken writes, increase calculations on write, , take more space. make sure need benefit , can measure helps you.