(For this question, I am using AWS/Aurora MySQL with a reasonably-spec'd RDS instance)
Consider the following schema:
Table T:
col0: the usual autoincrement primary key
col1: varchar
col2: varchar
col3: varchar
col4...N: various data
Consider that there is a unique index on:
<col1, col2, col3>
And a non-unique index on:
<col1, col2>
And consider the following query:
SELECT * FROM T
WHERE
(col1 = 'val1' AND col2 = 'id1') OR
(col1 = 'val2' AND col2 = 'id2') OR
...
(col1 = 'valN' AND col2 = 'idN');
I would (perhaps naively) expected MySQL to figure out that each element of the OR set matched the (non-unique) index, and performed the query in the way it would have if I had said:
WHERE col0 in (v1, v2, ... , vN)
But it doesn't seem to do that: the timing for these two queries is WAY OFF, on the order of 10x slower for the "or of ands" query. EVEN WITH the secondary key lookup, and the fact that it's a string column lookup, 10x seems a bit severe. Note that EXPLAIN claims to be using the correct/expected index whether I specify (col1, col2) or (col1, col2, col3)
Please note also that:
SELECT * from T
WHERE
col1 in (list1)
AND
col2 in (list2);
Is also slow when there are a lot of different values in list1 and list2. Doing an "and" for the three columns is almost intractably slow.
Perhaps not surprisingly, this query works better than the "or of ands" when list1 is of length 1.
col1
,col2
and then join against it might the highest performing way.