Reputation: 27283
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
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
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