Полные CREATE TABLE, статистику по данным - общую и в разрезе отдельных условий, планы всех запросов - в студию.

Добавил в опись CREATE TABLE скрипты. Проблема которая описана, начала появлятся когда общее количество записей в таблице news превысело 800К.

А статистику данных и планы запросов? что анализировать-то? На пустых таблицах я вижу dbfiddle.uk/okdQv6T2

Добавил в описание скриншот

Чисто по тексту запроса хочется предложить индексы: news (type, id, public_date) и news_rayon (rayon_id, news_id). Посмотри, как их создание повлияет на план и время выполнения. Если они не используются, посмотри на время с хинтами на эти индексы. dbfiddle.uk/VAuPX0ae

Индексы соданы. Я них несколько раз пересоздавал. Когда я убираю условие news_rayon.rayon_id = 1 тогда все выполняеться мгновенно

Покажите план исполнения в обоих вариантах с EXPLAIN ANALYZE. Похоже, основное время занимает сортировка из-за LIMIT. Если принудительно использовать news_public_date_index - тогда нет сортировки по public_date. Попробуйте добавить индекс news `news_type_public_date_index` (`type`,`public_date`). Тогда JOIN пройдет гладко после фильтра по news_rayon.

@Akina почему type, public_date а не наоборот public_date, type ? type не особо селективен, При подавляющем числе type=news индекс может проигноироватся. Плюс при отсутствии фильтра по типу второй обеспечит и выборку, и нормальную сортировку. зы: и id внутри не ясно зачем. в середине причем

зы: я короче за IX_public_date_type (public_date, type) и type ENUM ('news', ...) конечно же

в таблице связи еще в целом можно было обойтись составным первичным ключом (либо индекс на region_id,news_id). Вдобавок если опираться на то, что id новостей возрастают так же как дата, то попробовать сортировать по news_id связной таблицы. Это для случаев, когда индекс на основной игнорируется и первая таблица в explain будет не news, а news_region. Ибо в таком случае основная таблицы и поле для сортировки различны, что влечет создание временной таблицы.

Вопрос был к @Akina, но все же отвечу, поскольку я за (`type`,`public_date`). Первым type для фильтра. Селективность не так важна, как избежание сортировки по public_date.

@ValNik если среди миллиона строк таблицы будет тебе 800к новостей, то не будет этот индекс использоваться для фильтра, прочитает таблицу целиком, это быстрее будет. Если фильтра по type в запросе в целом не будет, то индекс тоже будет игнорироваться. При фиксированном type, сортировка будет работать с индексом что на (type, date) что на (date,type), но в отсутствии фильтра по type индекс (type,date) для сортировки использован не будет

можно сделать индекс и (type,date) но для обеспечения нормальной работы придется добавлять отдельный индекс чисто на date

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

Для показанного запроса мои индексы - покрывающие. Приведённый мной fiddle с планом показывает, что они используются - то есть сервер не обращается к самим таблицам вообще. А вот от финальной сортировки ну никуда не убежать.

Возможно, сортировка и есть самая дорогая операция в запросе. Ее как-то нужно избежать. Но @Hlud09 так и не показал стоимости.

@ValNik она там сама дорогая у него, потому что план выполнения запроса меняет порядок таблиц, и сортируется не по полю основной таблицы. С нормальным индексами на таблице новостей если возьмет ее первой, то и сортировка временной таблицы не потребует.

@Akina по поводу покрывающего, мне кажется, что автор просто убрал все остальные поля из селекта. И в целом id, как ПК таблицы, в индексе и так всегда содержится, для покрывающего его специально добавлять туда не надо искусственно. А вот в order by его наличие вероятно помешает, поскольку первое поле будет константой, второе не определено, и сортировка по итогу по третьему полю индекса.

@teran план выполнения запроса меняет порядок таблиц, и сортируется не по полю основной таблицы Запрос - INNER JOIN, так что ни основных, ни не-основных таблиц тут просто нет. В каком порядке серверу захочется, в таком и будет сканировать. Пока не появится STRAIGNT_JOIN.

@Akina так о том и речь, что порядок джойна который выберет сервер (а он выберет первой скорее таблицу, где меньше записей), может влиять на возможность использования индекса при сортировке.

Я извиняюсь. Не имел возможности участвовать в обсуждении. @Akina прав. Я оставил мимнимальный запрос, для простого понимания. Сам запрос имеет 4 Join и в селекте около 15 полей. Добавил индекс (type, date) - изменений скорости не последовало. @Hlud09 так и не показал стоимости - Я не до конца понимаю, что я еще должен показать

EXPLAIN ANALYSE со стоимостями этапов: (dev.mysql.com/doc/refman/8.0/en/explain.html)

@user364778 попробуй сейчас

Ты почему-то с другого аккаунта в чат зашел

Это оба твои аккаунта?

ru.stackoverflow.com/help/merging-accounts

@Hlud09 @user364778 заходи любым - у обоих права есть