Stack Stack - 5 months ago 10
SQL Question

Can a query only use one index per table?

I have a query like this:

( SELECT * FROM mytable WHERE author_id = ? AND seen IS NULL )
UNION
( SELECT * FROM mytable WHERE author_id = ? AND date_time > ? )


Also I have these two indexes:

(author_id, seen)
(author_id, date_time)





I read somewhere:


A query can generally only use one index per table when process the
WHERE
clause


As you see in my query, there is two separated
WHERE
clause. So I want to know, "only one index per table" means my query can use just one of those two indexes or it can use one of those indexes for each subquery and both indexes are useful?

In other word, is this sentence true?

"always one of those index will be used, and the other one is useless"

Answer

That statement about only using one index is no longer true about MySQL. For instance, it implements the index merge optimization which can take advantage of two indexes for some where clauses that have or. Here is a description in the documentation.

You should try this form of your query and see if it uses index mer:

SELECT *
FROM mytable
WHERE author_id = ? AND (seen IS NULL OR date_time > ? );

This should be more efficient than the union version, because it does not incur the overhead of removing duplicates.

Also, depending on the distribution of your data, the above query with an index on mytable(author_id, date_time, seen) might work as well or better than your version.