mysql - Can a query only use one index per table? -

i have query this:

( select * mytable author_id = ? , seen null ) union ( select * mytable author_id = ? , date_time > ? ) 

also have these 2 indexes:

(author_id, seen) (author_id, date_time) 

---

i read somewhere:

a query can use 1 index per table when process where clause

as see in query, there 2 separated where clause. want know, "only 1 index per table" means query can use 1 of 2 indexes or can use 1 of indexes each subquery , both indexes useful?

in other word, sentence true?

"always 1 of index used, , other 1 useless"

that statement using 1 index no longer true mysql. instance, implements index merge optimization can take advantage of 2 indexes where clauses have or. here description in documentation.

you should try form of query , see if uses index mer:

select * mytable author_id = ? , (seen null or date_time > ? ); 

this should more efficient union version, because not incur overhead of removing duplicates.

also, depending on distribution of data, above query index on mytable(author_id, date_time, seen) might work or better version.