Optimal MySQL indexes and query for selecting 3 rows based on 3 conditions-CodePudding

I have a MySQL table products with ~10k rows looking like this:

 ---- ----- -------- ------ --------- 
| id | cat | color  | size |  descr  |
 ---- ----- -------- ------ --------- 
|  1 |   1 | red    |    1 | Lorem 1 |
|  2 |   1 | green  |    2 | Lorem 2 |
|  3 |   2 | orange |    1 | Lorem 3 |
|  4 |   2 | blue   |    3 | Lorem 4 |
 ---- ----- -------- ------ ---------

I need to select 3 specific records based on 3 conditions with a single MySQL query. There are always will be exactly 3 records satisfying all 3 conditions.

Here is the query I came up with that works:

SELECT 
  category, 
  color, 
  size, 
  descr 
FROM 
  products 
WHERE 
  (
    cat = 1 
    AND color = 'red' 
    AND size = 1
  ) 
  OR (
    cat = 2 
    and color = 'orange' 
    and size = 1
  ) 
  OR (
    cat = 2 
    AND color = 'blue' 
    AND size = 3
  )

Question 1: Is this the most optimal query to retrieve the 3 records based on 3 conditions?

Question 2: What would be the optimal index(s) structure for this table?

Thanks in advance!

CodePudding user response：

I'd create an index on (cat, color, size). The order of columns in the index doesn't matter in this case, since you use = for the comparisons in all cases.

MySQL's IN() predicate supports row constructor comparison syntax, and current versions of MySQL do support index optimization for row constructor expressions (old versions of MySQL didn't, but those versions are all past their end-of-life by now).

SELECT 
  category, 
  color, 
  size, 
  descr 
FROM 
  products 
WHERE (cat, color, size) IN (
    (1, 'red', 1),
    (2, 'orange', 1),
    (2, 'blue', 3)
)