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_1SELECT * 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.
2
Answers
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.
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