single query using OR operator vs multiple queries using equals operator

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?

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.

StackExchange Database Administrators Q#80296, answer score: 7