mysql index usage for query with OR

Question

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.

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.

Answer 1

It is hard for databases to optimize OR. One method is to use UNION or 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.

Answer 2

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

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'