MySQL: как индексировать “ИЛИ” пункт
Я выполняю следующий запрос
SELECT COUNT(*)
FROM table
WHERE field1='value' AND (field2 >= 1000 OR field3 >= 2000)
Существует один индекс по field1, и другой составил композит по field2&field3.
Я вижу, что 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.
2 ответа
Типичный способ разбить предикаты OR - с помощью UNION .
Обратите внимание, что ваш пример не подходит для ваших индексов. Даже если вы опустите field1 из предиката, у вас будет field2> = 1000 OR field3> = 2000 , в котором нельзя использовать индекс. Если бы у вас были индексы на (поле1, поле2) и (поле1, поле3) или поле2 или поле3 отдельно, вы бы получили достаточно быстрый запрос.
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)
Я здесь новенький, поэтому не могу комментировать посты других людей, но это связано с постами Дэвида М. и soulmerge.
Временная таблица не нужна. UNION, предложенный Дэвидом М., не учитывает дважды, поскольку UNION подразумевает отдельный (т.е. если строка существует в одной половине объединения, игнорируйте ее в другой). Если бы вы использовали UNION ALL, вы бы получили две записи.
По умолчанию для UNION из результата удаляются повторяющиеся строки. Необязательное ключевое слово DISTINCT не имеет никакого эффекта, кроме значения по умолчанию, поскольку оно также определяет удаление повторяющихся строк. С необязательным ключевым словом ALL удаление повторяющихся строк не происходит, и результат включает все совпадающие строки из всех операторов SELECT.