skip to Main Content

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.

2

Answers


  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.

    Login or Signup to reply.
  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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search