This week, a client complained about a long-running query, and we needed to fix it.

While I can’t divulge the actual query, I can give an idea of its nastiness, which we inherited when we took over the project. The query itself doesn’t matter, so don’t overanalyze it.

Depending on the id’s used, this query was taking 1.5 minutes or more complete. On a website, this might as well be eternity.

How did we fix this?

I started by using EXPLAIN to try and understand where the bad parts where. Then I started breaking the query down into parts.

For example, I see a UNION join happening.

So I ran the query before the UNION first, and it was VERY fast. And EXPLAIN agreed that it should be fast.

I then did the same on the query after the UNION and explain had an entirely different answer. It was showing that it needed to analyze about 50 million records.

So the question is WHY?!?!?!

Having experience in query optimization, I immediately thought to look at the OR clause in the criteria.

I took out the second part of the OR clause and it ran VERY fast.

I put it back in and took out the first part, and again the query was VERY fast.

So we decided to just run the query twice, and use PHP to join the data sets correctly. We could probably still do this in SQL if we wanted to as well with another UNION join, but the query is already nasty enough.

Bottom line is that we should be careful when using OR in your WHERE Clauses. They are mighty convenient and when your dataset is small, you will not notice the impact. But as your data grows, you will start to discover performance problems. Hopefully this will help you solve them before you pull your hair out.

Some extra reading

• Exoscale: Understanding MySQL Queries with Explain
• W3Schools: MySQL AND, OR and NOT Operators