如何在 SQL 中使用更好的日期比较来优化此查询？
我对这个查询有问题，这个查询的复杂性不好，我使用这个查询很长时间，现在这个数据库有很多行可以通过这种方法进行选择。所有索引均已正确添加。我搜索了一些其他方法来通过日期比较来优化此查询，因为这是此解决方案中的瓶颈。
SELECT (...) FROM table
WHERE (YEAR(row_add_date) * 10000 +
MONTH(row_add_date) * 100 +
DAYOFMONTH(row_add_date)) >= (var_0 * 10000 + var_1 * 100 + var_2) and
(YEAR(row_add_date) * 10000 +
MONTH(row_add_date) * 100 +
DAYOFMONTH(row_add_date)) <= (var_3 * 10000 + var_4 * 100 + var_5)
有人可以帮助我吗？ 问候
I have problem with this query, complexity of this query is not good, i use this query from long time and now this database have many rows to get selecting by this method. All index'es is added propertly. I searching some other method to optimize this query by date comparsion because this is bottleneck in this solution.
SELECT (...) FROM table
WHERE (YEAR(row_add_date) * 10000 +
MONTH(row_add_date) * 100 +
DAYOFMONTH(row_add_date)) >= (var_0 * 10000 + var_1 * 100 + var_2) and
(YEAR(row_add_date) * 10000 +
MONTH(row_add_date) * 100 +
DAYOFMONTH(row_add_date)) <= (var_3 * 10000 + var_4 * 100 + var_5)
Can anyone help me?
Greetings
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我建议使用内置的 mysql 日期比较。
但请注意，这首先是一个奇怪的测试：不仅仅是测试日期是否等于 10 月 27 日，如下所示：
I'd suggest using built-in mysql date comparisons.
But note that this is a strange test in the first place: aren't just testing that the date is equal to October 27th, like this:
你为什么要这样分解日期？每行函数不能很好地扩展。在我看来，最后的整个日期部分可以替换为：
即使您想要一个范围，您仍然最好按原样使用日期。
根据您的编辑表明您正在使用变量，您应该在条件右侧进行计算。这是因为这将在查询开始之前完成一次。有了你所拥有的，左侧的计算将每行完成一次，这绝对是性能杀手。
Why are you breaking apart the date like that? Per-row functions do not scale well. It seems to me that the entire date section at the end can be replaced by:
Even if you want a range, you're still better of using the dates as they are.
Based on your edits to state that you're using variables, you should do the calculation on the right-hand side of the conditions. That's because this will be done once before the query start. With what you have, the calculation on the left side will be done once per row, a definite performance killer.
我猜测 row_add_date 的类型是
datetime。如果是这样，您需要将 20101027 转换为日期时间，并将该列与该列进行比较。换句话说：
I'm going to guess that row_add_date is of type
datetime. If so, you need turn 20101027 into adatetime, and compare the column to that.In other words: