Stack Stack - 1 year ago 60
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 )
( 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

As you see in my query, there is two separated
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 Source

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:

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download