MySQL: OR versus UNION – which is faster? MySQL: OR versus UNION – which is faster?

I must say, this came as a shock to me, until I looked at the results and thought about it for a minute.  Before jumping in, let me explain how and where I encountered this.  I was recently working on a project called Deja Scene – The actor to actor movie database.  The purpose of this site is to find two actors who have starred in multiple movies together.

Because the nature of the algorithm an actor can be either on the left side of the connection or the right side.

Let’s look at an example, if I search for George Clooney, I will find that he has 372 Deja Scene connections.  Here was the original query I used to retrieve the list of his connections:


SELECT *
FROM  `actor2_actors`
WHERE actor1_id =1
OR actor2_id =1

Seems pretty straight forward; however, I was seeing really slow performance and I couldn’t figure out why.  I created an index on both the actor1_id and the actor2_id, so I assumed Mysql was properly indexing this.  Boy was I wrong.  Look at the results when I use the EXPLAIN function:


or_results

As you can see, Mysql thinks it can be my key that I created, but it doesn’t use it!  No wonder it’s not performing well.

I scratched my head for a bit and thought a UNION query would accomplish the same thing and would be able to better recognize the index.

I proceeded to delete the original index that had both ids together and created two indexes, one for each id.

My query now became:


EXPLAIN SELECT *
FROM `actor2_actors`
WHERE actor1_id = 1
UNION SELECT *
FROM `actor2_actors`
WHERE actor2_id = 1

Now check out the EXPLAIN results, each side of the union is using my indexes, exactly what I want to happen.

union_results

After implementing this fix, performance was fixed and my Google graph from webmaster tools is showing me this as well!

google_results

Because I was using a framework, the UNION added a bit of development effort because I was going outside the framework, but never let this deter you from seeking the best way to do something!

Published on Mar 24, 2014

Tags: SQL Tutorials for Beginners, Intermediate and Advanced Users | Optimization | performance

Related Posts

Did you enjoy this article? If you did here are some more articles that I thought you will enjoy as they are very similar to the article that you just finished reading.

Tutorials

Learn how to code in HTML, CSS, JavaScript, Python, Ruby, PHP, Java, C#, SQL, and more.

No matter the programming language you're looking to learn, I've hopefully compiled an incredible set of tutorials for you to learn; whether you are beginner or an expert, there is something for everyone to learn. Each topic I go in-depth and provide many examples throughout. I can't wait for you to dig in and improve your skillset with any of the tutorials below.