MySQL: как индексировать предложение "ИЛИ"
Я выполняю следующий запрос
SELECT COUNT(*)
FROM table
WHERE field1='value' AND (field2 >= 1000 OR field3 >= 2000)
Существует один индекс над полем1, а другой - над полем 2 и полем.
Я вижу, что MySQL всегда выбирает индекс field1, а затем делает соединение, используя два других поля, что довольно плохо, потому что ему необходимо объединить 146 000 строк.
Предложения о том, как улучшить это? Благодаря
(ИЗМЕНИТЬ ПОСЛЕ ПОДАЧИ ПРЕДОСТАВЛЯЮЩЕГО РЕШЕНИЯ)
На основе предложенного решения я видел это на Mysql, играя с этим.
SELECT COUNT(*) FROM (SELECT * FROM table WHERE columnA = value1
UNION SELECT * FROM table WHERE columnB = value2) AS unionTable;
намного медленнее, чем выполнить:
SELECT COUNT(*)
FROM table
WHERE (columnA = value1 AND columnB = value2)
OR (columnA = value1 AND columnC = value3)
Имея два составных индекса:
index1 (columnA,columnB)
index2 (columnA,columnC)
Интересно, что Mysql "объясняет" запрос, который всегда занимает index1 в обоих случаях, а index2 не используется.
Если я изменил индексы на:
index1 (columnB,columnA)
index2 (columnC,columnA)
И запрос к:
SELECT COUNT(*)
FROM table
WHERE (columnB = value2 AND columnA = value1)
OR (columnC = value3 AND columnA = value1)
Тогда это самый быстрый способ, которым я нашел Mysql.
Ответы
Ответ 1
Типичным способом разбиения предикатов OR является UNION.
Обратите внимание, что ваш пример не соответствует вашим индексам. Даже если вы опустили field1 из предиката, у вас будет field2 >= 1000 OR field3 >= 2000, который не сможет использовать индекс. Если у вас есть индексы на (field1, field2) и (field1,field3) или field2 или field3 отдельно, вы получите достаточно быстрый запрос.
SELECT COUNT(*) FROM
(SELECT * FROM table WHERE field1 = 'value' AND field2 >= 1000
UNION
SELECT * FROM table WHERE field1 = 'value' AND field3 >= 2000) T
Обратите внимание, что вы должны указать псевдоним для производной таблицы, поэтому подзапрос будет псевдонимом как T.
Реальный пример. Имена столбцов и таблиц были анонимизированы!
mysql> SELECT COUNT(*) FROM table;
+----------+
| COUNT(*) |
+----------+
| 3059139 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM table WHERE columnA = value1;
+----------+
| COUNT(*) |
+----------+
| 1068 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM table WHERE columnB = value2;
+----------+
| COUNT(*) |
+----------+
| 947 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM table WHERE columnA = value1 OR columnB = value2;
+----------+
| COUNT(*) |
+----------+
| 1616 |
+----------+
1 row in set (9.92 sec)
mysql> SELECT COUNT(*) FROM (SELECT * FROM table WHERE columnA = value1
UNION SELECT * FROM table WHERE columnB = value2) T;
+----------+
| COUNT(*) |
+----------+
| 1616 |
+----------+
1 row in set (0.17 sec)
mysql> SELECT COUNT(*) FROM (SELECT * FROM table WHERE columnA = value1
UNION ALL SELECT * FROM table WHERE columnB = value2) T;
+----------+
| COUNT(*) |
+----------+
| 2015 |
+----------+
1 row in set (0.12 sec)
Ответ 2
Я новичок здесь, поэтому не могу комментировать сообщения других людей, но это связано с сообщениями Дэвида М. и soulmerge.
Временная таблица не нужна. UNION Дэвид М. предположил, что двойной счет не считается, поскольку UNION подразумевает отличную (т.е. Если строка существует в одной половине объединения, игнорируйте ее в другом). Если вы использовали UNION ALL, вы получите две записи.
Поведение UNION по умолчанию состоит в том, что дубликаты строк удаляются из результата. Дополнительное ключевое слово DISTINCT не имеет никакого эффекта, кроме значения по умолчанию, поскольку оно также указывает удаление дубликатов строк. С необязательным ключевым словом ALL удаление дубликатов строк не происходит, и результат включает все соответствующие строки из всех операторов SELECT.
http://dev.mysql.com/doc/refman/5.0/en/union.html
