Optimizing for an OR in a Join in MySQL

Question

I've got a pretty complex query in MySQL that slows down drastically when one of the joins is done using an OR. How can I speed this up? the relevant join is:

LEFT OUTER JOIN publications p ON p.id = virtual_performances.publication_id 
                               OR p.shoot_id = shoots.id

Removing either condition in the OR decreases the query time from 1.5s to 0.1s. There are already indexes on all the relevant columns I can think of. Any ideas? The columns in use all have indexes on them. Using EXPLAIN I've discovered that once the OR comes into play MySQL ends up not using any of the indexes. Is there a special kind of index I can make that it will use?

Answer 1

Make two joins on the same table (adding aliases to separate them) for the two conditions, and see if that is faster.

select ..., coalesce(p1.field, p2.field) as field
from ...
left join publications p1 on p1.id = virtual_performances.publication_id
left join publications p2 on p2.shoot_id = shoots.id

Answer 2

This is a common difficulty with MySQL. Using OR baffles the optimizer because it doesn't know how to use an index to find a row where either condition is true.

I'll try to explain: Suppose I ask you to search a telephone book and find every person whose last name is 'Thomas' OR whose first name is 'Thomas'. Even though the telephone book is essentially an index, you don't benefit from it -- you have to search through page by page because it's not sorted by first name.

Keep in mind that in MySQL, any instance of a table in a given query can make use of only one index, even if you have defined multiple indexes in that table. A different query on that same table may use another index if the optimizer reasons that it's more helpful.

One technique people have used to help in situations like your is to do a UNION of two simpler queries that each make use of separate indexes:

 SELECT ...
 FROM virtual_performances v
 JOIN shoots s ON (...)
 LEFT OUTER JOIN publications p ON (p.id = v.publication_id)
UNION ALL
 SELECT ...
 FROM virtual_performances v
 JOIN shoots s ON (...)
 LEFT OUTER JOIN publications p ON p.shoot_id = s.id;

Answer 3

You can also try something like this on for size:

SELECT * FROM tablename WHERE id IN (SELECT p.id FROM tablename LEFT OUTER JOIN publications p ON p.id IN virtual_performances.publication_id) OR p.id IN (SELECT p.id FROM tablename LEFT OUTER JOIN publications p ON p.shoot_id = shoots.id);

It's a bit messier, and won't be faster in every case, but MySQL is good at selecting from straight data sets, so repeating yourself isn't so bad.