Существуют две таблицы:
maindata = id, devid, value (10 млн рядов)
djournal = id, devid, md_id_begin, md_id_end, state (10к рядов)
Я хочу выбрать все из maindata для некоторых devid кроме строк с неправильными state:
SELECT md.*
FROM maindata AS md
LEFT JOIN djournal AS dj
ON md.id BETWEEN dj.md_id_begin AND dj.md_id_end
AND md.devid = dj.devid
WHERE md.devid = 123456789
AND dj.state <> 'idle'
ORDER BY md.id ASC;
Данный запрос дает именно то, что я хочу, но медленно. Созданы все возможные индексы. Конечно, хранить легко state поле прямо в maindata table, но любопытно, почему этот запрос такой медленный и существует ли какое-либо обходное решение?
У вас просто проблема с индексом.
Вы не публиковали структуру базы данных, но если вы задаете этот вопрос, это потому, что вы мало знаете о базах данных (потому что каждый достойный сервер базы данных может показать вам, где запрос тратит свое время).
Ваши недостающие индексы, вероятно, включены md_id_begin, md_id_end так же как state. Просто предположение.
Индексирование id также может быть очень хорошей идеей, если вы этого не сделаете.
Извините за беспокойство, люди, для этой проблемы нет решения. Это вообще не проблема, это нормальное поведение sql-engine. Я пытаюсь объяснить почему. Пусть у нас есть два набора:
mysql> select * from Q; mysql> select * from R;
+----+------+ +----+------+
| id | val | | id | val |
+----+------+ +----+------+
| 1 | a | | 1 | a |
| 2 | b | | 2 | b |
| 3 | c | | 3 | c |
| 4 | d | | 4 | d |
| 5 | e | | 5 | e |
+----+------+ +----+------+
Разрешить СОЕДИНЕНИЕ без условий:
mysql> SELECT Q.val AS Qval, R.val AS Rval FROM Q JOIN R;
+------+------+
| Qval | Rval |
+------+------+
| a | a |
| b | a |
| c | a |
| d | a |
| e | a |
| a | b |
| b | b |
| c | b |
| d | b |
| e | b |
| a | c |
| b | c |
| c | c |
| d | c |
| e | c |
| a | d |
| b | d |
| c | d |
| d | d |
| e | d |
| a | e |
| b | e |
| c | e |
| d | e |
| e | e |
+------+------+
25 rows in set (0.00 sec)
Выпрямляем JOIN условием "=":
mysql> SELECT Q.val AS Qval, R.val AS Rval FROM Q JOIN R ON Q.val = R.val;
+------+------+
| Qval | Rval |
+------+------+
| a | a |
| b | b |
| c | c |
| d | d |
| e | e |
+------+------+
5 rows in set (0.00 sec)
И когда мы ПРИСОЕДИНЯЕМСЯ к ">", мы получаем:
mysql> SELECT Q.val AS Qval, R.val AS Rval FROM Q JOIN R ON Q.val > R.val;
+------+------+
| Qval | Rval |
+------+------+
| b | a |
| c | a |
| d | a |
| e | a |
| c | b |
| d | b |
| e | b |
| d | c |
| e | c |
| e | d |
+------+------+
10 rows in set (0.00 sec)
Вялое состояние дает расслабляющий результат. Сложные условия уменьшают результирующий набор, но значительно увеличивают объем вычислений. Когда мы ПРИСОЕДИНЯЕМСЯ к BETWEEN или <или>, мы получаем огромные временные таблицы для промежуточных результатов - без индексов, поиск по файловой сортировке.
Так что объединять наборы чем-то еще, кроме "=" - плохая идея.