Index when using OR in query

Question

What is the best way to create index when I have a query like this?

... WHERE (user_1 = '$user_id' OR user_2 = '$user_id') ...

I know that only one index can be used in a query so I can't create two indexes, one for user_1 and one for user_2.

Also could solution for this type of query be used for this query?

WHERE ((user_1 = '$user_id' AND user_2 = '$friend_id') OR (user_1 = '$friend_id' AND user_2 = '$user_id'))

Answer 1

MySQL has a hard time with OR conditions. In theory, there's an index merge optimization that @duskwuff mentions, but in practice, it doesn't kick in when you think it should. Besides, it doesn't give as performance as a single index when it does.

The solution most people use to work around this is to split up the query:

SELECT ... WHERE user_1 = ?
UNION
SELECT ... WHERE user_2 = ?

That way each query will be able to use its own choice for index, without relying on the unreliable index merge feature.

Your second query is optimizable more simply. It's just a tuple comparison. It can be written this way:

WHERE (user_1, user_2) IN (('$user_id', '$friend_id'), ('$friend_id', '$user_id'))

In old versions of MySQL, tuple comparisons would not use an index, but since 5.7.3, it will (see https://dev.mysql.com/doc/refman/5.7/en/row-constructor-optimization.html).

P.S.: Don't interpolate application code variables directly into your SQL expressions. Use query parameters instead.

Answer 2

I know that only one index can be used in a query…

This is incorrect. Under the right circumstances, MySQL will routinely use multiple indexes in a query. (For example, a query JOINing multiple tables will almost always use at least one index on each table involved.)

In the case of your first query, MySQL will use an index merge union optimization. If both columns are indexed, the EXPLAIN output will give an explanation along the lines of:

Using union(index_on_user_1,index_on_user_2); Using where

The query shown in your second example is covered by an index on (user_1, user_2). Create that index if you plan on running those queries routinely.