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 > ? )
A query can generally only use one index per table when process theclause
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.