6

Case 1 - Single query using OR operator in where clause:

select * from users where name='smith' or nick='smith' (using index_merge )

Case 2 - multiple queries using equals operator in where clause:

select * from users where name='smith'; (using single index);

select * from users where nick='smith'; (using single index);

Q: Is there any performance loss in Case 2?

2

1 Answer 1

7

It's a common optimization to use UNION in these cases (at least when you must use a single query):

select * from users where name='smith' /* using single index */
UNION 
select * from users where nick='smith'; /* using single index */

In my experience, I don't like to rely on index_merge (union) because the performance is usually not as good as doing the explicit UNION trick like above.

But each case could be a little bit different, because the performance may depend on how many rows are matched by each condition.


@jynus is correct in his comment below that the issue is about a temporary table created for the UNION.

I also want to add that it's hard to make general rules for what type of query is faster. So much depends on how much data you're querying, the version of the software you use, your operating system and tuning, and how many queries per second are running concurrently.

The only reliable way to answer these sorts of questions is to test several solutions yourself, on your server, against your data.

1
  • @korean dreamer To expand why the performance difference, UNION will create in many cases a temporary table (in order to identify duplicates), which may be worse than the index merge. What it is clear is that 2 queries are almost never a good option, as you double the roundtrip time from the application and the SQL overhead (parsing, optimizing, sending back results, etc.).
    – jynus
    Oct 16, 2014 at 9:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.