Multiple MySQL columns in IN section
I have a database with four columns corresponding to geographic x, y coordinates for start and end position. Columns:
- 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 geographic pair combinations. How can I process 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 thought I could do something with the index? What would be the best approach (i.e. Quick Query)? Thank you for your help!
Notes:
- I cannot change the database schema
- I have about 100'000'000 lines
EDIT:
The as-is code does work, however it was very slow and didn't use the index (since we have an older MySQL version v5.6.27
).
source to share
I don't understand your point of view. The following request is valid. MySQL syntax:
SELECT *
FROM my_table
WHERE (x0, y0, x1, y1) IN ((4, 3, 5, 6), ... ,(9, 3, 2, 1));
I would suggest that MySQL will use the composite index you described. But if it isn't, you can 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
Equality comparison in a sentence WHERE
will use an index.
source to share
You can concatenate the four values into a string and test them like this:
SELECT *
FROM my_table
WHERE CONCAT_WS(',', x0, y0, x1, y1) IN ('4,3,5,6', ..., '9,3,2,1');
source to share
The way you are doing is giving correct results in the mysql version on my machine. I am using v5.5.55
. You may be using an older one. Please check it.
If you still want to fix this problem in your own version or the above solution doesn't work, then only read the following solution.
I still don't know about the datatypes and ranges of all your columns. So I am assuming that the datatype is an integer and the range is between 0 and 9. If so, you can easily do it as shown below.
select * from s1 where x0+10*x1+100*y1+1000*y2 in (4356,..., 9321);
source to share