I have the following table in MySQL that records event counts of stuff happening each day
我在MySQL中有下表，记录每天发生的事件的事件计数
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?"
我希望能够通过日期范围和星期几来有效地查询。例如 - “5月的星期二什么是event_count？”
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?
目前，event_date字段是日期类型。 MySQL中是否有任何函数可以让我按星期几查询此列，还是应该在表中添加另一列来存储星期几？
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).
该表将包含数十万行，因此如果有选择，我将选择最有效的解决方案（而不是最简单的解决方案）。
3 个解决方案
#1
56
Use DAYOFWEEK in your query, something like:
在您的查询中使用DAYOFWEEK，例如：
SELECT * FROM mytable WHERE MONTH(event_date) = 5 AND DAYOFWEEK(event_date) = 7;
This will find all info for Saturdays in May.
这将找到5月份周六的所有信息。
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).
只需先尝试上面的内容，看看它是否符合您的需求，如果没有，请添加一些额外的列并将数据存储在写入中。请注意更新异常（如果更改event_date，请确保更新day_of_week列）。
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.
请注意，非规范化字段将增加执行写入所需的时间，增加写入计算并占用更多空间。确保您确实需要这些好处，并且可以衡量它对您有何帮助。
#3
3
If you want textual representation of day of week - use DAYNAME() function.
如果您想要星期几的文本表示 - 请使用DAYNAME（）函数。
#1
56
Use DAYOFWEEK in your query, something like:
在您的查询中使用DAYOFWEEK，例如：
SELECT * FROM mytable WHERE MONTH(event_date) = 5 AND DAYOFWEEK(event_date) = 7;
This will find all info for Saturdays in May.
这将找到5月份周六的所有信息。
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).
只需先尝试上面的内容，看看它是否符合您的需求，如果没有，请添加一些额外的列并将数据存储在写入中。请注意更新异常（如果更改event_date，请确保更新day_of_week列）。
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.
请注意，非规范化字段将增加执行写入所需的时间，增加写入计算并占用更多空间。确保您确实需要这些好处，并且可以衡量它对您有何帮助。
#2
#3
3
If you want textual representation of day of week - use DAYNAME() function.
如果您想要星期几的文本表示 - 请使用DAYNAME（）函数。