How to Efficiently Use Multiple Columns in MySQL's WHERE IN Clause with Indexes?

MySQL Multiple Columns in IN Clause

Problem:

You have a database table with four columns representing geographical coordinates: x0, y0, x1, and y1. You have an index on these columns in the order x0, y0, x1, y1. You want to efficiently query the table using a list of coordinate pairs in a WHERE IN clause.

Solution:

To effectively use the index, rewrite the IN predicate as a series of OR conditions, where each condition compares the coordinates of the current row to a coordinate pair in the list.

Example:

Instead of:

(x0, y0, x1, y1) IN ((4, 3, 5, 6), (9, 3, 2, 1))

Use:

(  ( x0 = 4 AND y0 = 3 AND x1 = 5 AND y1 = 6 ) 
OR ( x0 = 9 AND y0 = 3 AND x1 = 2 AND y1 = 1 )
)

Optimization in Newer MySQL Versions:

In newer versions of MySQL, the optimizer has improved to better utilize indexes in such queries. The syntax:

(a,b) IN ((7,43),(7,44),(8,1))

has been supported for a long time, but performance issues existed previously. The newer optimizer now generates more efficient execution plans.

Note:

A similar optimization applies to OR constructs in WHERE clauses. For improved efficiency, consider using:

WHERE ( seq >= 7 ) 
  AND ( seq > 7 OR sub > 42 ) 

instead of:

WHERE ( seq > 7 ) 
  OR ( seq = 7 AND sub > 42 ) 

The above is the detailed content of How to Efficiently Use Multiple Columns in MySQL's WHERE IN Clause with Indexes?. For more information, please follow other related articles on the PHP Chinese website!