To make effective use of the index, we could rewrite the IN predicate
example
(x0, y0, x1, y1) IN ((4, 3, 5, 6),(9, 3, 2, 1))
Like this:
( ( x0 = 4 AND y0 = 3 AND x1 = 5 AND y1 = 6 )
OR ( x0 = 9 AND y0 = 3 AND x1 = 2 AND y1 = 1 )
)
EDIT
Newer versions of MySQL optimizer fix the performance problem; generate execution plans that make more effective use of available indexes.
The (a,b) IN ((7,43),(7,44),(8,1)) syntax has been supported in MySQL many versions back, but there were performance problems with it (at least with with non-trivial sets) because of the suboptimal execution plan generated by the optimizer.
But the optimizer has been improved in newer versions of MySQL; the newer optimizer can generate more efficient execution plans.
Note a similar related problem with OR constructs. Here’s an example query intended to get the “next page” of 20 rows ordered by columns seq and sub (unique tuple). The last fetched page (seq,sub)=(7,42)
With much older versions of MySQL, this syntax would not be accepted
WHERE (seq,sub) > (7,42)
ORDER BY seq, sub
LIMIT 20
And when MySQL did support the syntax, we would get an execution plan like if we had written
WHERE ( seq > 7 )
OR ( seq = 7 AND sub > 42 )
ORDER BY sub, seq
LIMIT 20
we would get a much more efficient the execution plan if we instead write something subtly different:
WHERE ( seq >= 7 )
AND ( seq > 7 OR sub > 42 )
ORDER BY sub, seq
LIMIT 20
and we would get a much better plan from the MySQL optimizer. we’d expect the optimizer plan to use available UNIQUE INDEX on (sub,seq), and return rows in index order from a range scan operation…