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.
name
andnick
?IN
Operator? Should perform roughly on a par withOR