MySQL multiple columns in IN clause

mysql sql where-in
nbeuchat picture nbeuchat · Jun 22, 2017 · Viewed 34.8k times · Source

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

spencer7593 picture spencer7593 · Jun 22, 2017

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

Related questions

MySQL WHERE IN Query - ORDER BY Match

I'm trying to rephrase my question, cause my last one wasn't clear to everyone. This is my Test Table +----------+---------+-------+ | rel_id | genre | movie | +----------+---------+-------+ | 1 | 1 | 3 | | 2 | 8 | 3 | | 3 | 3 | 3 | | 4 | 2 | 5 | | 5 | 8 | 5 | | 6 | 3 | 5 | | 7 | 1 | 8 | | 8 | 8 | 8 | | 9 | 3 | 8 | | 10 | 5 | 9 | | 11 | 7 | 9 | | 12 | 9 | 9 | | 13 | 4 | 9 | | 14 | 12 | 9 | | 15 | 1 | 10 | | 16 | 8 | 10 | | 17 | 3 | 10 | | 18 | 5 | 10 | | 19 | 1 | 11 | | 20 | 2 | 11 | | 21 | 8 | 11 | | 22 | 5 | 11 | | 23 | 3 | 11 | +----------+---------+-------+ Result should be in the following order if …

Read More
Get all rows matching a list in a SQL query

I have the following SQL query that selects any row matching ANY of the values in the list (9, 10): SELECT r.id, r.title, u.name as 'Created By', c.name as 'Category', c.value, cr.category_id FROM category_resource …

Read More
Why is this mySQL query extremely slow?

Given is a mySQL table named "orders_products" with the following relevant fields: products_id orders_id Both fields are indexed. I am running the following query: SELECT products_id, count( products_id ) AS counter FROM orders_products WHERE orders_…

Read More