MySQL несколько столбцов в предложении IN

У меня есть база данных с четырьмя столбцами, соответствующими географическим координатам x, y для начальной и конечной позиции. Столбцы следующие:




  • x0

  • y0

  • x1

  • y1


У меня есть индекс для этих четырех столбцов с последовательностью x0, y0, x1, y1.

У меня есть список примерно из ста комбинаций географических пар. Как бы я мог эффективно запрашивать эти данные?

Я хотел бы сделать что-то вроде этого, как предложено на это так Ответ но он работает только для базы данных Oracle, а не MySQL: 



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


Я подумал, что можно было бы что-то сделать с индексом? Каков будет наилучший подход (т. е. самый быстрый запрос)? Спасибо за вашу помощь!



Примечания:




  • я не могу изменить схему базы данных

  • у меня есть около 100'000'000 строк


Редактировать:
Код как есть фактически работал, однако он был чрезвычайно медленным и не использовал преимущества индекса (поскольку у нас есть более старая версия MySQL v5.6.27).

542   5  

5 ответов:

Чтобы эффективно использовать индекс, можно переписать предикат IN 

(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 )
)
6  
2017-06-22 20:57:48

Я не понимаю вашей точки зрения. Следующий запрос является допустимым синтаксисом MySQL: 

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

Я ожидал бы, что MySQL будет использовать составной индекс, который вы описали. Но, если это не так, вы могли бы сделать: 

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

Сравнение равенства в предложении WHERE будет использовать преимущество индекса.

4  
2017-06-22 20:56:34

MySQL позволяет сравнивать конструкторы строк, как вы показываете, но оптимизатор не знал, как использовать индекс для повышения производительности до MySQL 5.7.

4  
2017-06-22 21:17:47

Вы можете объединить четыре значения в строку и проверить их следующим образом: 

SELECT * 
FROM my_table 
WHERE CONCAT_WS(',', x0, y0, x1, y1) IN ('4,3,5,6', ..., '9,3,2,1');
1  
2017-06-22 20:50:52

Способ, которым вы это делаете, дает правильные результаты в версии mysql на моей машине. Я использую v5.5.55. Может быть, вы используете более старый. Пожалуйста, проверьте это.

Если вы все еще хотите решить эту проблему в своей собственной версии или вышеупомянутое решение не работает, то только прочитайте следующее решение.

Мне до сих пор не ясны типы данных и диапазон всех ваших столбцов здесь. Поэтому я предполагаю, что тип данных-целое число, а диапазон - от 0 до 9. Если это так, то вы можете легко сделать это, как показано ниже.

select * from s1 where x0+10*x1+100*y1+1000*y2 in (4356,..., 9321);
0  
2017-06-22 21:14:58

Comments

    Ничего не найдено.
Click here to cancel reply.