MariaDB / MySQL Performance Tuning and Optimization: Avoiding ORs in Queries

When it comes to MySQL performance tuning and optimization, you probably already know that you should avoid using ORs in your WHERE clause. It makes it difficult or impossible for the query optimizer to come up with a good execution plan to make the query run fully indexed. But what can you actually do about it when you functionally really need that OR and you need the query to run fast? Well, as it turns out, it is sometimes possible to have your cake and eat it! Here is a technique our MySQL support specialists often use to improve query performance in cases where using ORs is necessary.

Consider the following query:

SELECT  *
FROM    tx
JOIN    protocol ON tx.transaction_id = protocol.transaction_id
WHERE   (tx.stat = 2 OR protocol.stat = 2) AND
        tx.create_date < DATE_SUB(NOW(), INTERVAL 5 MINUTE);

Because the optimizer cannot sensible execute on the search against (tx.stat = 2 OR protocol.stat = 2), it can only try to execute based on the index on tx.create_date using a range scan. It turns out this is quite expensive and the query takes about 4 seconds, which is quite slow. So how can we avoid the OR? We can can turn the query unto a UNION of two queries, each matching one of the OR-ed elements.

SELECT *
FROM   tx
JOIN   protocol ON tx.transaction_id = protocol.transaction_id
WHERE  tx.stat = 2 AND
       tx.create_date < DATE_SUB(NOW(), INTERVAL 5 MINUTE)
UNION
SELECT *
FROM   tx
JOIN   protocol ON tx.transaction_id = protocol.transaction_id
WHERE  protocol.stat = 2 AND
       tx.create_date < DATE_SUB(NOW(), INTERVAL 5 MINUTE);

The first query in the UNION can be executed against the index on tx(stat, create_date), and the second query can be executed against the index on protocol(stat). Net result? The two UNION-ed queries execute in 40ms on the system in question instead of 4s like the original single query with OR. Sometimes, two queries can be faster than one.

Call us if you need help with your MySQL performance tuning and optimization.