Making MySQL OR performance better
MySQL experts and Old SQL hands look away now. This is like teaching grannies to suck eggs, but if you’ve always had experience of advanced (which used to mean commercial) SQL databases then you probably don’t know this trick.
In the old days performance for OR clauses was pretty bad and a lot of DBMSs would ignore the index and start table scanning and performance would stink.
The old (and MySQL) way to get around this if the problem exhibits itself is to simply use a UNION and have each part of the OR on each side.So for example for table employee we need to find any employee where their name is ’stuart’ or ‘roger’ (i’m assuming we’re using an OR here - we could of course use an IN..).
The first version is the clear version
SELECT * FROM employee WHERE forename = 'stuart' OR forename = 'roger'
However, if you do an EXPLAIN on it and notice that it’s table scanning it’s trivial to re-write as a UNION
SELECT * FROM employee WHERE forename = 'stuart' UNION SELECT * FROM employee WHERE forename = 'roger'
Now if you know about your data (and the OR condition) then you can optimise a dash further. UNION merges the two result sets and checks for duplicate entries (de-dupes - just like a DISTINCT) before returning the values, but if you’re sure that the two sets won’t clash (and above is a great example) then use UNION ALL which ignore the de-duping stage.
So, why not always use a UNION? Well firstly it’s not clear and secondly there’s a duplication of code. Imagine if your OR clause was more complex (forename could be ’stuart’, ‘bob’, ‘roger’, ‘terry’, ‘june’) then that’s 5 repeats of the same statement. Throw in the fact that you’ll often explicitly mention the columns you want (remember i’m using a * in the SELECT clause for example only - try your best not to do this in real code of course) and that’ll you’ll have multiple joins then you realise you only want to do this if and when you get a performance problem.
Have fun and I hope not too many of you are sucking eggs at the moment 