В NFS версии 3 и 4 есть отличия в размере блока данных, передаваемых за один раз. NFSv3 использует блок размером 4 КБ, в то время как NFSv4, как правило, поддерживает более крупные блоки, в частности 32 КБ. Более крупные блоки в NFSv4 могут потенциально повысить производительность при передаче больших файлов, так как требуется меньше операций для передачи того же объема данных.
В MySQL это Controlling the Query Optimizer. Ну и у Машки что-то похожее - искать лень.
Вообще-то зря. Они развелись настолько давно, что их следует воспринимать как принципиально разные, хотя и высокосовместимые, СУБД.
А зачем спешить переходить? Переходите тогда, когда результаты тестирования новой версии вас устроят. Сейчас похоже рано (хотя я не верю в оптимизатор, который никогда не ошибается).
Например, некоторые до сих пор используют mysql 5.7, и не только потому что все подводные камни известны, просто 8ка показывает себя хуже при тестировании. И несмотря на проблемы 5.7, стабильность важнее.
Я бы не рассматривал эту проблему как некий баг 11 версии.
А считаю, что это такой стандартный взбрык оптимизатора. Просто вот он вылез именно на этом запросе после апгрейда. Так совпало.
Я думаю, имеет смысл написать куда-нибудь в группу поддержки MariaDB. Приложив результаты show index from table для всех таблиц.
Обновить и актуализировать статистику, чтобы не ждать пока оно само наберётся, можно запросом ANALYZE TABLE.
Влиять на план выполнения отдельного запроса вы можете тюнингом - либо хинтами запроса (как вы использовали USE INDEX, ещё можете попробовать STRAIGHT_JOIN), либо хинтами оптимизатора. Либо вы можете настроить работу оптимизатора глобально, тюнингом используемого "комплекта" стоимостей.
Если в вашем случае "куча" - это более десятка, то рассмотрите вариант с сохранением этого списка во временной индексированной таблице и использованием INNER JOIN либо WHERE EXISTS вместо WHERE IN.
Сам недавно столкнулся с точно таким же поведением. Мария (впрочем, когда работал с Му, то же самое случалось) точно так же перекособочила порядок таблиц в запросе и получила какое-то дикое время выполнения. Заставил использовать мой порядок джойнов через STRAIGHT_JOIN, и всё залетало. Но вот как это лечить на системном уровне - и сам не знаю. Принимаю такие закидоны как данность и лечу вот хинтами.
Причём у меня БД не менялась, это новый запрос на старых данных.
Хм. Кстати, а что за настройки-то?
Ну и традиционно - в первую очередь размер innodb_buffer_pool_size?
Может быть, 11-ке тупо требуется больше памяти, индекс не влезает в оперативку и она срывается в построчное чтение? Может, я конечно глупость говорю, но лишний раз проверить буфер innodb всё равно не помешает.
оптимизатору чтобы построить хороший план, нужно понимать какие данные и в какой пропорции лежат в таблицах. Тупой пример, вы залили в таблицу много данных, а оптимизатор думает что она маленькая и вместо использования индекса пойдет фул-сканом. Разумеется это не так глупо работает, но при больших и быстрых изменениях лучше все таки вручную попросить собрать статистику, на всякий случай.
Указать какой индекс использовать - нормально. Но, нужно быть очень аккуратным, если вы знаете что распределение данных не изменится, то хорошо. Чаще всего, в работающем проекте распределение данных более менее постоянно, и указание конкретных индексов надежнее.
И еще, USE - это рекомендация, оптимизатор может на нее забить, прям чтоб гарантировано нужен FORCE.
Но бывают случаи, когда пользователь может быть удален и создан заново, например при регистрации по номеру телефона - когда пользователь долго не пользуется номером и потом номер передается другому человеку. Тогда признак уникальности надо снять и добавить SoftDeletes.
Так-то да...запилил софт...запили и оплату за свой же "софт"... :)