вот и в MariaDB

но у них там свой оптимизатор

Ну вот они теперь заменяют IN(...константные значения...) на  IN(...фиктивный подзапрос сгенерированный на лету...) - в чём прикол? Почему это вдруг становится быстрее?

Sergei может знать =)

@confguru теперь лучше? ;)

Ага

Спасибо

Эта оптимизация применяется если количество элементов в in больше чем in_subquery_conversion_threshold=10000. Для маленького количество элементов применяется index dives https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html (меньше eq_range_index_dive_limit=200), чтобы узнать сколько же строчек мы получим для каждого значения из IN. При этом потребляется 125 или 700 байт на каждое значение в in, но не более чем range_optimizer_max_mem_size байт на весь запрос. Таким образом, без использования "select * from (VALUES (1,2),(3,4)) t1 (c1,c2);" (этого синтаксиса нет в mysql) приходилось делать явную временную таблицу с которой джойнить оригинальный запрос без IN c тысячами значений. Почему становится быстрее: если index dives не сработали (из-за количества элементов или памяти), то приходится игнорировать условие с IN. Это может быть к лучшему, а может и приводить к сильному замедлению на табличках с миллионами-миллиардами строк.

Быстрее с in_subquery_conversion_threshold становится, т.к. на временной табличке со значениями будет статистика и не надо каждое значение из IN проверять на количество строк.

Статистика на временной таблице - это интересно, не знал, спасибо!

> Статистика на временной таблице
я про create temporary table ids_tmp (id int primary key); insert into ids_tmp values (1),(2),...,(19834); select * from t inner join ids_tmp on t.id = ids_tmp.id; вместо select * from t where id in (1,...,19834);

неявные таблички https://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html не имеют индексов, так что кроме количества строк у оптимизатора ничего нет по ним.

Раньше был такой совет. Допустим у меня большая таблица с товарами (100тыщ строк), пользователь на сайте выставляет фильтры, результат ему нужно выдать с пажинацией по 100шт. Я на PHP формирую два запроса. Первый выбирает исключительно ID товаров SELECT ID FROM goods WHERE ...фильтры пользователя... ORDER BY price LIMIT 100; - говорят, если выбирать только ID, то движку будет проще. А вторым запросом по уже найденной сотне ID я выбираю всю информацию о товарах: SELECT * FROM goods WHERE ID IN (...здесь прямым текстом подставлены ids найденные предыдущим запросом...) ORDER BY price

Это имеет какой-то смысл в современных реалиях или это «мифы оптимизации»?

И таких IN запросов у меня полно по всей системе

это late row lookup, https://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/ надо смотреть explain для конкретного запроса. вместо двух запросов достаточно subselect.

Спасибо за ссылку, сейчас почитаю

обычно большие списки id лезут из внешних систем, типа sphinx search или от хранения массивов в виде текстовых списков, вместо табличек связи один ко многим

Прочитал статью, всё очень доходчиво расписано, похоже я эмулирую subselect на PHP