MySQL nested And/Or condition index

Question

I have a query

SELECT count(*) FROM my_table WHERE column_A = x AND (column_B = y OR column_C = z)

Question:

Is it possible to index column A,B,C in such a way that this query only uses index to finish. If not, what is the best way to index these 3 columns?

Thanks a lot

Answer 1

INDEX(A,B,C)

but do not also have INDEX(A); the Optimizer may pick the "shorter" index instead of the "covering" index.

Selectivity does not play any role. So INDEX(A,C,B) is just as good. And the only indexes that will be considered are those starting with A.

Elaboration

OR cannot use an index, however a "covering index" can help. Let me explain. A "covering index" is one that has all the columns needed in the query. But why does that matter? Well, if you have, say, just INDEX(A), the Index is used to find the rows in the Index's BTree that have the appropriate value(s) for A, then, for each such row, it reaches over into the data's BTree to find the entire row. At that point, it can check B=y OR C=z by brute force.

If, instead, you have INDEX(A,B,C), then all 3 of those columns are in the Index BTree and the full check can be performed without reaching into the data's Btree. (It is still brute force when it comes to the OR, but the secondary lookup is avoided.)