Why is the index not being used?

Question

I have a table (say table t) with an index on col_a that is unique.

Now the index on col_a is used in these 2 following cases -

• SELECT * from t WHERE(col_a,col_b) IN((x,y),(p,q)); - query_1
• SELECT * from t WHERE(col_a = x and col_b = y) OR (col_a = p and col_b = q); - query_2

But not when I query like so -

• SELECT * from t WHERE(col_b,col_a) IN((y,x),(q,p)); - query_3

In fact, I don't find any difference in query style of query_1 and query_2 and query_3. Aren't they functionally the same for MySQL? I was expecting the index to be used here.

Answer 1

For most of MySQL's existence, "row constructors" were poorly optimized. Only recently have some obvious use cases been optimized. I guess your case was missed.

Answer 2

Because the writing style of (col_1, col_b) uses tuple, which follow the leftmost prefix principle, col_ The index of a can be used, col_ The index of b cannot be used