CODEWITHSUNDEEP
mysqlindexing
Ender
Ender

Reputation: 27283

How many indexes will actually get used?

I'm writing a page that does very simple search queries, resulting in something like:

SELECT * FROM my_table WHERE A in (a1, a2, a3) AND B in (b1, b2) AND C in (c1, c2, c3, c4) AND

And so on for a variable number of columns, usually ~5. If I create a separate index for each column (one for A, one for B, one for C, not (A,B,C)), will all of them be used in the above query?

Upvotes: 2

Views: 267

Answers (2)

Crozin
Crozin

Reputation: 44406

Just check it:

EXPLAIN SELECT * FROM ...;

You'll get a list of executed queries, their types, used indexes, and so on...

Upvotes: 0

Konerak
Konerak

Reputation: 39773

The optimizer will try to use an Index Merge operation and use the three indices.

Execute the statement with EXPLAIN before it to see the indices it can use, and will use. Consider FORCE INDEX and see if it makes a difference.

EXPLAIN SELECT * FROM my_table WHERE A in (a1, a2, a3) AND B in (b1, b2) AND C in (c1, c2, c3, c4) AND

Upvotes: 3

Related Questions