Оптимизация запроса mysql — exists vs in?

Question

[nelauvetau]

Есть два запроса - один с EXISTS, второй с IN.

SELECT bl.id
FROM book_list bl
WHERE bl.active=1
AND EXISTS (SELECT id_book FROM book_codes bc WHERE bc.id_book = bl.id and bc.code LIKE '%f1%')
ORDER BY bl.id desc;

SELECT bl.id
FROM book_list bl
WHERE bl.active=1
AND bl.id in (SELECT id_book FROM book_codes bc WHERE bc.id_book = bl.id and bc.code LIKE '%f1%')
ORDER BY bl.id desc;

Не могу понять, почему первый запрос эффективнее, когда в строке для поиска через LIKE один символ, но чем больше символов я ищу через LIKE, тем первый запрос медленнее.
Второй запрос всегда отрабатывает приблизительно за одно и то же время.

Неважно, какой размер строки для поиска через LIKE, первый запрос по explain перебирает в book_codes порядка 10тыс записей, а второй - порядка 4мл по одному и тому же ключу id_book.
В book_codes > 4млн записей.

UPD:
Изначально я написала запрос через JOIN

SELECT bl.id
FROM book_list bl
JOIN book_codes bc on bc.id_book = bl.id 
WHERE bl.active=1 AND bc.code LIKE '%ww%'
GROUP BY bl.id
ORDER BY bl.id desc;

Но на кодревью JOIN завернули и предложили

AND bl.id in (SELECT id_book FROM book_codes bc WHERE bc.id_book = bl.id and bc.code LIKE '%f1%')

Explain всех трех запросов


UPD UPD
По полю bс.code тоже есть индекс

Comments

[Владислав]

напишите перед запросом (не подзапросом)
EXPLAIN SELECT ...
это немного прояснит поведение бд.

[nelauvetau]

Владислав Фурсов, это было первым моим телодвижением в попытках решить этот вопрос

Неважно, какой размер строки для поиска через LIKE, первый запрос по explain перебирает в book_codes порядка 10тыс записей, а второй - порядка 4мл по одному и тому же ключу id_book.

[Владислав]

nelauvetau, а нельзя приложить EXPLAIN сюда?

[Vitsliputsli]

nelauvetau,

Но на кодревью JOIN завернули и предложили

А как объяснили отказ от join в пользу in?

[Dimonchik]

А как объяснили отказ от join в пользу in?

вот-вот, самая частая ошибка - предположение, что IN срабатывает быстрее

[Владислав]

Возможно кто-то прочёл о том что джоины плохо и теперь их боится, но эта информация уже не актуальна много лет(я не говорю конечно о сотнях джоинов).
JOIN намного эффективнее разбирается анализатором запросов, чем подзапросы и ошибиться в построении запроса сложнее используя JOIN.
Но конечно, лучше написать несколько разных запросов и протестировать их, замерить время выполнения, оценить explain, чем просто выбирать на обум.

У меня было в практике, что подзапрос в
SELECT (SELECT...) FROM table
был эффективнее JOIN но без тестов это было бы не реально проверить.

[nelauvetau]

Vitsliputsli, "запросик на проде долго выполняется" :(

[Владислав]

nelauvetau, как видно из EXPLAIN, что при IN, что при JOIN, анализатор запросов построил запросы практически идентично, только при IN используется доп временная таблица для построения semijoin (Start temporary, End temporary)

[Владислав]

nelauvetau, долго выполняется не из за JOIN, а из за LIKE, ведь из за него приходиться сканировать всю таблицу, чтобы отфильтровать, индексы тут не помогают. Может лучше стоит попробовать воспользоваться полнотекстовым поиском по данному столбцу за место LIKE?

[nelauvetau]

Владислав Фурсов,

так в этом и загвоздка, что в случае с IN или JOIN скорость выполнения всегда приблизительно одинаковая, какой бы длины строчку в LIKE туда не поставили - около 2сек.

а вот EXISTS просто умирает с увеличением LIKE

[Владислав]

nelauvetau,

По полю bс.code тоже есть индекс

Он не используется и использоваться не может, так как используется LIKE, а не прямое сравнение(о чём нам собственно говорит EXPLAIN своим KEY==NULL).

[Владислав]

nelauvetau, как я помню, EXISTS выполняется на каждую запись, то есть
в случае EXISTS будет так:
1. Выбираем запись из бд.
2. Вызываем подзапрос EXISTS.
3. Если в подзапросе вернули хотябы одну запись, пишем эту запись во временную таблицу, иначе откидываем
4. Повторить с пункта 1 пока не пройдём по всем записям.
5. Вывести то что насобирали.

В случае с IN/JOIN:
1. Выполняем подзапрос и храним результат во временной таблице.
2. Выбираем записи у которых bl.id есть в нашей временной таблице из пункта 1 (тут очень хорошо помогают индексы и они используются).
3. Выводим.

[Владислав]

nelauvetau, кстати, именно из за того, что EXIST не нужны все записи, а нужна хотя бы одна, EXPLAIN говорит нам про 100% фильтрацию, которая конечно же хороша, но вот не задача, приходится выполнять этот запрос 4млн. раз...

[Vitsliputsli]

запросик на проде долго выполняется

Тогда это не код-ревью, к сожалению. Действительно, для разных ситуаций будут предпочтительнее разные запросы, join для 2 больших таблиц, in если вторая маленькая, в общем случае. Хотя здесь скорее всего виноват like, как самый прожорливый, и индекс по полю не поможет, т.к. вы ищите не начало строки, а вхождение.

[Владислав]

nelauvetau, возвращаясь к сути вопроса

когда в строке для поиска через LIKE один символ, но чем больше символов я ищу через LIKE, тем первый запрос медленнее.

Тут как раз особенность работы EXIST и LIKE показывают себя с плохой стороны.
При том, что EXIST достаточно хотя бы одной записи, чтобы показать результат, то при LIKE, происходит скан всей таблицы для поиска хотя бы одного вхождения, и чем больше символов, тем сложнее поиск и походу, дальше по таблице находится результат.

То есть например у нас есть

аааа
аабб
бббб
ббсс
сссс
ссдд
аадд

и при %а% поиск остановится на первой строке, то при поиске %ад%, придётся впустую просканировать всю таблицу(и это для каждой записи из основной таблицы, то есть 4млн раз...).

Answer 1

[Владислав]

Мне кажется, вы не верно используете EXIST и из за этого не верно выполняется запрос.
А зачем здесь собственно IN/EXIST, кажется этот же запрос будет более эффективен, если использовать JOIN.

SELECT bl.id
FROM book_list bl
JOIN book_codes bc ON bc.id_book = bl.id
WHERE bl.active=1
AND bc.code LIKE '%f1%'
ORDER BY bl.id desc;

Answer 2

[Андрей]

А зачем подзапрос?
Не будет быстрее сделать inner join book_codes, а в where добавить проверку кода?

Answer 3

[Dimonchik]

в первом еиу достаточно пройти
SELECT code FROM book_codes where code LIKE '%f1%'
и при TRUE выполить остальные XOR: bc.id_book = bl.id, bl.active=1 что практически мгновенно, пока не будет найден первый TRUE для EXISTS

во втором же приходится создавать массив id_book & bc.code ( он и создает его на все 4млн что есть), а затем прогонять по нему bl.id

Comments

[nelauvetau]

я поэтому и решила переписать с IN на EXISTS, потому что мне по факту важно лишь наличие для bl.id хотя бы одного bc.code по условию LIKE, чтобы вытащить этот bl.id

но я не понимаю, почему время выполнения EXISTS с 0.005сек при LIKE ‘%x%’ увеличивается до 6сек при LIKE ‘%xxxxx%’

[Dimonchik]

длина bcode какая?

ну и like медленный, когда % в начале - никаких индексов для такого нет , это не Кликхаус

поделайте просто поиск bcode с like и сравните - будет та же самая картина

[nelauvetau]

dimonchik2013, так в этом и загвоздка, что в случае с IN или JOIN скорость выполнения всегда приблизительно одинаковая, какой бы длины строчку в LIKE туда не поставили - около 2сек.

а вот EXISTS просто умирает с увеличением LIKE

забыла уточнить - по полю bс.code есть индекс (как и по bc.id_book)