Что быстрее: данные из подзапроса или из LEFT JOIN

205

13 марта 2019, 09:00

Реализую двухступенчатый поиск в большой БД на MySQL: сначала беглый по хэшу, затем детальный по всем полям. Какой из следующих вариантов SQL-запроса будет работать быстрее? Индекс в таблице составной из ID и хэша (хотя ID у всех уникальный, хэш только для скорости обработки).

1) Передавать данные напрямую из вложенного запроса:

SELECT <детальная проверка по 150 полям> AS matched_2
FROM (
    SELECT ID, <150 полей>, <первичная проверка по хэшу> AS matched_1
    FROM table
    HAVING matched_1 > 0.5
) AS basic
HAVING matched_2 > 0.7
ORDER BY matched_2

2) Или же на внешний приделать LEFT JOIN:

SELECT <детальная проверка по 150 полям> AS matched_2
FROM (
    SELECT ID, <первичная проверка по хэшу> AS matched_1
    FROM table
    HAVING matched_1 > 0.5
) AS t1
LEFT JOIN (
    SELECT ID, <150 полей> FROM table
) AS t2
ON t1.ID = t2.ID
HAVING matched_2 > 0.7
ORDER BY matched_2

Любые другие советы по оптимизации очень приветствуются.

Answer 1

Правда (выявленная экспериментальным путём) оказалась чуточку сложнее и интереснее: у каждого варианта есть свой оптимальный вариант применения.

Если после первого этапа поиска остаётся ещё много^ строк, то быстрее работает прямая передача данных (в моём примере 39.8 vs 46.5 secs).
Если после фильтрации остаётся немного^ строк, то использование LEFT JOIN'a оправдывает себя (14.5 vs 16.5 secs).

^ – конечно, понятия много/немного очень индивидуальны, для каждого проекта надо смотреть конкретные числа.

Предполагаю, что время исполнения этих двух вариантов кода можно математически описать так:

T1 = N * s1 + N * r + F * s2
T2 = N * s1 + F * r + F * s2 + F * j

Где:

N – количество строк в таблице
F – количество строк после первой фильтрации
s1, s2 – время фильтрации одной строки в первом и втором этапе
r – время считывания тех 150 полей для одной строки
j – время на обработку JOIN'a одной строки

Тогда выбор оптимального варианта будет определяться знаком неравенства между N * b и F * b + F * j – а единственный изменяющийся параметр здесь это F, от него-то всё и зависит: чем он меньше, тем более выгоден второй вариант, и наоборот.

Создание интернет-магазина спортивных товаров в ABCname

ПОСЛЕДНИЕ ВОПРОСЫ

Все вопросы

ТОП-10

Все популярные вопросы

Ограничить количество логов FIFO

У меня логи пишутся в базу (yii\log\DbTarget), это удобно, но со временем накапливаются тысячи старых логов которые вообщем-то уже не нужны и только...

203

HTML