使用两个布尔条件的MySQL查询要比分别查询每个条件所需的时间长得多

I have a MySQL database with a table with about 160.000 entries. When I query the following queries it takes about 0.5 seconds of exeuction time:

我有一个MySQL数据库，有一个包含160.000个条目的表。当我查询以下查询时，大约需要0.5秒的时间:

SELECT * FROM table WHERE EXTRACT(HOUR FROM date) > 18;
SELECT * FROM table WHERE EXTRACT(HOUR FROM date) < 3;

After combining the statements into a single query like this

将语句组合成一个这样的查询之后

SELECT * FROM table WHERE EXTRACT(HOUR FROM date) > 18 OR EXTRACT(HOUR FROM date) < 3;

the execution takes forever. In fact I haven't seen the query finish execution and canceld it after about 20 minutes.

执行需要永远。实际上，我还没有看到查询执行完毕并在大约20分钟后取消它。

When I change the OR to AND for example like this:

当我将OR改为，例如:

SELECT * FROM table WHERE EXTRACT(HOUR FROM date) > 10 AND EXTRACT(HOUR FROM date) < 19;

Execution time is in the milliseconds again.

执行时间也是以毫秒为单位。

Can somenone please explain this odd behavior and maybe suggest a solution to querying the two conditions with OR.

有人能解释一下这种奇怪的行为吗?或者提出一个解决问题的方法。

Thank you in advance

提前谢谢你

2 个解决方案

#1

This will work for you

这对你有用

SELECT * FROM table WHERE EXTRACT(HOUR FROM date) not between 3 and 18;

#2

The performances of a query like yours cannot be optimal: you are using a function on a date column, and even if the date column is indexed, the index cannot be used.

像您这样的查询的性能不能是最佳的:您正在日期列上使用一个函数，而且即使日期列被索引，也不能使用索引。

As a simple workaround I would try with an union all query:

作为一个简单的解决方案，我将尝试使用union all query:

SELECT * FROM table WHERE EXTRACT(HOUR FROM date) > 18
UNION ALL
SELECT * FROM table WHERE EXTRACT(HOUR FROM date) < 3

while logically equivalent to the OR form, it might perform better... but it also might not!

虽然在逻辑上等价于或形式，但它可能表现得更好……但也有可能不是!

A proper solution is to add a new hour column, with an index on it:

一个合适的解决方案是增加一个新的小时列，列上一个索引:

ALTER TABLE table ADD COLUMN date_hour INT;
ALTER TABLE table ADD INDEX idx_date (date_hour);

/* do an update once... or use a trigger */
UPDATE table SET date_hour = EXTRACT(HOUR FROM date);

SELECT * FROM table WHERE date_hour > 18 or date_hour < 3;

#1