MySQL IN子句中的多列 [英] MySQL multiple columns in IN clause

问题描述

我有一个数据库，该数据库具有四列，分别对应于开始和结束位置的地理坐标x，y.列为:

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

我有这四个列的索引，分别为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?

我想按照此SO答案，但仅适用于Oracle数据库，不适用于MySQL:

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!

注意:

• 我无法更改数据库的架构
• 我有大约100'000'000行

原来的代码实际上在工作，但是它非常慢并且没有利用索引(因为我们有旧版本的MySQL v5.6.27).

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

推荐答案

要有效使用索引，可以重写IN谓词

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

像这样:

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

这篇关于MySQL IN子句中的多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！