MySQL multiple columns in IN clause

Question

I have a database with four columns corresponding to the geographical coordinates x,y for the start and end position. The columns are:

• x0
• y0
• x1
• y1

I have an index for these four columns with the sequence x0, y0, x1, y1.

I have a list of about a hundred combination of geographical pairs. How would I go about querying this data efficiently?

I would like to do something like this as suggested on this SO answer but it only works for Oracle database, not MySQL:

SELECT * FROM my_table WHERE (x0, y0, x1, y1) IN ((4, 3, 5, 6), ... ,(9, 3, 2, 1)); 

I was thinking it might be possible to do something with the index? What would be the best approach (ie: fastest query)? Thanks for your help!

Notes:

• I cannot change the schema of the database
• I have about 100'000'000 rows

EDIT: The code as-is was actually working, however it was extremely slow and did not take advantage of the index (as we have an older version of MySQL v5.6.27).

Answer 1

To make effective use of the index, you could rewrite the IN predicate

(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 ) ) 

Answer 2

I do not understand your point. The following query is valid MySQL syntax:

SELECT * FROM my_table WHERE (x0, y0, x1, y1) IN ((4, 3, 5, 6), ... ,(9, 3, 2, 1)); 

I would expect MySQL to use the composite index that you have described. But, if it doesn't you could do:

SELECT * FROM my_table WHERE x0 = 4 AND y0 = 3 AND x1 = 5 AND y1 = 6 UNION ALL . . . SELECT * FROM my_table WHERE x0 = 9 AND y0 = 3 AND x1 = 2 AND y1 = 1 

The equality comparisons in the WHERE clause will take advantage of an index.