Can a query only use one index per table?

Question

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 1

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.

Answer 2

UNION combines results of subqueries. Each subquery will be executed independent of others and then results will be merged. So, in this case WHERE limits are applied to each subquery and not to all united result.

In answer to your question: yes, each subquery can use some index.