带OR的查询的mysql索引用法

I've got a query like this:

我有这样的查询:

SELECT account.name, address.first_line 
FROM account
JOIN address ON address.id = account.address_id
WHERE
( account.date_updated >= '2016-05-05 12:00:00' 
OR
address.date_updated >= '2016-05-05 12:00:00' )

both the user table and the address table have an index on their respective 'date_updated' columns but when I do an explain neither of these indexes are being used.

用户表和地址表都有各自'date_updated'列的索引,但是当我做一个解释时,这些索引都没有被使用。

Is there any way to change this? Or do I simply have to run two separate queries to get the information I want with the indexes being used.

有没有办法改变这个?或者我只需要运行两个单独的查询来获取我想要的信息与使用的索引。

I think this is different to this question: MySQL: how to index an "OR" clause because the fields on either side of the "OR" are from different tables and so you can't put a composite index on them.

我认为这与这个问题不同:MySQL:如何索引“OR”子句,因为“OR”两侧的字段来自不同的表,因此您不能在它们上面放置复合索引。

2 个解决方案

#1

It is hard for databases to optimize OR. One method is to use UNION or UNION ALL:

数据库很难优化OR。一种方法是使用UNION或UNION ALL:

SELECT a.name, ad.first_line 
FROM account a JOIN
     address ad
     ON ad.id = a.address_id
WHERE a.date_updated >= '2016-05-05 12:00:00' 
UNION ALL
SELECT a.name, ad.first_line 
FROM account a JOIN
     address ad
     ON ad.id = a.address_id
WHERE ad.date_updated >= '2016-05-05 12:00:00' AND
      a.date_updated < '2016-05-05 12:00:00'

You can write this query using UNION rather than UNION ALL and leave out the NOT EXISTS clause. However, the duplicate elimination in UNION is often slower than just removing the possible duplicates using the WHERE clause.

您可以使用UNION而不是UNION ALL编写此查询,并省略NOT EXISTS子句。但是,UNION中的重复消除通常比使用WHERE子句删除可能的重复项要慢。

#2

MySQL does not use indexes for WHERE clauses that have OR in them. This question has more information. MySQL WHERE clause documentation.

MySQL不对其中包含OR的WHERE子句使用索引。这个问题有更多信息。 MySQL WHERE子句文档。

I would try to union the two together like

我会尝试将两者合并在一起

SELECT account.name, address.first_line FROM account 
JOIN address ON address.id = account.address_id 
WHERE account.date_updated >= '2016-05-05 12:00:00'

UNION

SELECT account.name, address.first_line FROM account 
JOIN address ON address.id = account.address_id 
WHERE address.date_updated >= '2016-05-05 12:00:00'

#1