How many indexes will actually get used?

Question

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?

Answer 1

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

Answer 2

Just check it:

EXPLAIN SELECT * FROM ...;

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