После обновления марии до 10.9-версии FORCE INDEX перестал работать.

Как известно, оптимизатор запросов не всегда определяет верно индексы, которые нужно использовать. Это происходит ещё и потому, что оптимизатор учитывает объём доступной памяти, а она может быть занята другими выполняющимися запросами. Поэтому часто возникают "плавающие баги", когда один и тот же запрос то использует нужные индексы, то нещадно тормозит. USE INDEX в этом случае бессмысленно использовать — оптимизатор его просто игнорирует.

Почему же FORCE INDEX перестал работать? Оказывается изменился его синтаксис.

Новый синтаксис: FORCE INDEX [{FOR {JOIN|ORDER BY|GROUP BY}] ([index_list]).

Новый синтаксис предполагает, что если проиндексированный столбец находится не в WHERE, а в JOIN или ORDER BY или в GROUP BY, то это нужно указывать явно, иначе оптимизатор и не почешется.

Рис. 1. EXPLAIN выборки без {FOR ORDER BY}

Рис. 2. EXPLAIN выборки c {FOR ORDER BY}

Как видно из Рис. 1, FORCE INDEX не сработал, так как в WHERE не было поля с указанным ключём (updated), а после явного указания, что ключ нужно искать в ORDER BY — всё заратало.

Почему EXPLAIN не показывает индекс updated, а показывает индекс type, для меня осталось загадкой, однако, на самом деле используется именно updated, так как type тут „null” и „не null” — ускорить выполнение запроса он никак не может, а данный запрос на миллионах записей выполнятся довольно быстро.

Каким бы был идеальный FORCE INDEX?

FORCE INDEX не даёт разработчику полного управления планом выполнения запроса.

Во первых, список индексов должен указывать, что нужно использовать все индексы в указанном порядке. Сейчас же оптимизатор волен решать: какой индекс из списка ему брать. Другими словами нужен AND, кроме существующего уже OR. Например: FORCE INDEX (PRIMARY OR (updated AND PRIMARY AND type)).

Во вторых, синтаксис FORCE INDEX было бы немного изменить, чтобы прописывать порядок индексов при исполнении всех операций, а не указывать только для одной.
Например:

FORCE INDEX 
  FOR ORDER BY (updated)
  FOR WHERE (type)

В третьих, движок базы при выполнении запросов должен уметь делать составные индексы (из нескольких полей и, в том числе, из разных таблиц) для конкретных запросов и хранить их в своём кеше. Тогда последующие запросы резко ускорятся.

Источники

Документация mariadb / FORCE INDEX / https://mariadb.com/kb/en/force-index/.
Документация mysql / Подсказки индексов / https://dev.mysql.com/doc/refman/8.0/en/index-hints.html.

FORCE INDEX {FOR ORDER BY}