MYSQL DATE 函数在 LEFT JOIN 中运行速度极慢
当添加行：
LEFT JOIN core_records_sales as sales ON DATE(appointments.date) = DATE(sales.date_sold)
到我的查询中时，脚本的运行时间从大约 8 秒增加到 2-3 分钟。
是否有某些数据导致此问题，或者我没有正确实现该功能？
我需要使用 DATE() 因为我需要它们是同一天，但日期字段是 DATETIME
When adding the line:
LEFT JOIN core_records_sales as sales ON DATE(appointments.date) = DATE(sales.date_sold)
To my query, it boosts the time for the script to run from about 8 seconds, to 2-3 minutes.
Would there be some data causing this problem or am I not implementing the function correctly?
I need to use DATE() because I need them to be the same day but the date fields are DATETIME
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这几乎肯定是约会.date 字段被索引的问题。添加 DATE() 函数的使用使得索引无法使用，从而强制进行表扫描。
我以前必须处理这个问题，我发现解决这个问题的最好方法是有一个单独的列，其中只有日期部分（没有时间），或者将日期和时间存储在两个单独的列中。
我很想听听其他人是否有更好的方法来处理这个问题。
This is almost certainly an issue with the appointments.date field being indexed. Adding the use of the DATE() function makes it so that index cannot be used, forcing a table scan.
I've had to deal with this before, and the best way I've found to solve the issue is to have a separate column with just the date part (no time) in it or to store the date and time in two separate columns.
I'd love to hear if others have a better way of dealing with that though.
您在日期列上有索引吗？没有索引的连接需要全表扫描，因此这可能是性能问题的原因。
通过 EXPLAIN 运行查询以查看查询优化器提出了查询计划。
Do you have an index on the date column? Joining without an index requires a full table scan, so that's likely the cause of the performance issue.
Run your query through an EXPLAIN to see what the query optimizer comes up with for the query plan.
这可能是因为您强制对两个表进行全表扫描，而之前您使用索引作为查询的一部分。您需要检查该计划（并向我们提供更多详细信息），因此通过 EXPLAIN 运行它以查看发生了什么。
It's probably because you're forcing a full table scan on both tables whereas previously you were using an index as part of the query. You need to examine the plan (and provide us with a little more detail too) so run it through EXPLAIN to see what's happening.