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