(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:
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.