MYSQL DATE函数在LEFT JOIN中运行缓慢 [英] MYSQL DATE function running insanely slow in LEFT JOIN

问题描述

添加行时：

LEFT JOIN core_records_sales as sales ON DATE(appointments.date) = DATE(sales.date_sold)

对我的查询，它提高了脚本从大约8秒运行到2秒的时间-3分钟。

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?

我需要使用DATE（），因为我需要它们是同一天，但日期字段是DATETIME

I need to use DATE() because I need them to be the same day but the date fields are DATETIME

推荐答案

这是几乎肯定是约会条件字段被索引的一个问题。添加使用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.

这篇关于MYSQL DATE函数在LEFT JOIN中运行缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！