Geek Answers Handbook

Can a query use only 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 > ? )

I also have these two indexes:

(author_id, seen)
(author_id, date_time)

I read somewhere:

Typically, a query can use only one index for each table when processing a proposal. WHERE

As you can see in my request, there are two divided sentences WHERE. So I want to know that “only one index per table” means that my query can use only one of these two indexes, or can it use one of these indexes for each subquery and both indexes are useful?

In other words, is this sentence true?

"one of these indices will always be used, and the other is useless"

+4
source share
4

MySQL. , , where, or. - .

, mer:

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

, union, .

, mytable(author_id, date_time, seen) , .

+2

UNION . , . , WHERE , .

: , .

+2

, select, . , .

+1

Each individual subquery or part of a compound query itself, a query can be evaluated as a single query to access performance and index .. you can also force another index to be used for an eahc query. In your case, you are using union and these are two separate queries. Combined into the resulting query, you can get a short guide on how mysql ue .. is indexed in this guide

http://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html

+1
source

More articles:


All Articles