I have been busy changing some SQL queries around so that they look more readable to the human eye, i was also told that they would be maybe 5-10% faster.
The previous SQL statements would look like this.
SELECT * FROM teams WHERE Team1='Joe Bloggs' OR Team2='Joe Bloggs' OR Team3='Joe Bloggs'
I changed it to
SELECT * FROM teams WHERE 'Joe Bloggs' IN (Team1,Team2,Team3)
The new query is approx 10 times slower, after checking what could be the cause i found that it was not using any indexes and even if i tried to force an index it still wouldn't use it.
The table has approx 120,000 rows and i cannot change the table format as other applications i dont have access to, use it. The Team1,Team2,Team3 columns are all VARCHAR(45)
Could anybody explain why the indexes are used for the original query but not the new one? I've read a ton of pages but cannot find the answer, i've read that its possible that mysql is determining that its faster not to use the index, however that shouldnt be the case here as the IN query is almost 10x slower.
Multiple ORs SELECT (run 1000 times with no Cache) - 12.863906860352 elapsed IN SELECT (run 1000 times with no Cache) - 122.73787903786 elapsed
Thank you for your time.
In the query:
SELECT * FROM teams WHERE 'Joe Bloggs' IN (Team1,Team2,Team3)
you are comparing (looking up) a bunch of columns against a string literal. The optimizer would typically use an index on the search target, in this case Joe Bloggs, to find a value in the IN clause. But, it can't put an index on a string literal. So, everything is reversed here, and this is why the index won't help.
On the other hand, in your first query:
SELECT * FROM teams WHERE Team1='Joe Bloggs' OR Team2='Joe Bloggs' OR Team3='Joe Bloggs'
MySQL would grab the string literals, and then look them up against the various columns using a B-tree index. This behaves as you would expect and are seeing.