Можно ли оптимизировать данный запрос или смотреть в сторону денормализации?

113

25 мая 2021, 09:00

Данный SQL запрос имеет различные модификации в коде, но суть одна - он работает на вывод объявлений, с информацией о категории и региональности.

Для наглядности покажу изображение:

Вот сам запрос. В нем нет ничего сложного, это JOIN на несколько таблиц и условия в WHERE:

explain SELECT STRAIGHT_JOIN *
    FROM `advert`
    INNER JOIN `user-country` ON `advert`.`advert_place_country` = `user-country`.`id`
    INNER JOIN `user-region` ON `advert`.`advert_place_region` = `user-region`.`id`
    INNER JOIN `user-city` ON `advert`.`advert_place_city` = `user-city`.`id`
    INNER JOIN `user` ON `user`.`id` = `advert`.`advert_id_user`
    INNER JOIN `category` ON `advert`.`advert_category` = `category`.`id`
    WHERE
        `advert_active` = 1 
        AND `advert`.`advert_payment` = 1 
        AND `advert_place_country` = 3159 
        AND `user`.`user_active` = 1
    ORDER BY
        `advert`.`advert_create_date`
    DESC
LIMIT 0, 80

Вот explain:

Меня не устраивает время обработки запроса в 0.3 секунды и кол-во затрагиваемых строк. Индекс такой:

`findListForCatalog` (
    `advert_active`,
    `advert_payment`,
    `advert_place_country`,
    `advert_place_region`, // в данном запросе не используется 
    `advert_place_city`    // в данном запросе не используется
),

Я сейчас не могу понять, что делать - либо думать о денормализации данных и создавать триггеры, который будут информацию региональности и категории писать в таблицу объявлений adverts, либо я что-то не так делаю?

В таблице всего 20 000 записей, но время работы в 0.3 меня совсем не устраивает.

Answer 1

Предлагаю попробовать следующий вариант:

SELECT *
  FROM (
    SELECT advert.*, user.... (нужные поля из user)
      FROM `advert`
     INNER JOIN `user` ON `user`.`id` = `advert`.`advert_id_user`
     WHERE `advert_active` = 1 
       AND `advert`.`advert_payment` = 1 
       AND `advert_place_country` = 3159 
       AND `user`.`user_active` = 1
     ORDER BY `advert`.`advert_create_date` DESC
     LIMIT 0, 80
  ) advert
 INNER JOIN `user-country` ON `advert`.`advert_place_country` = `user-country`.`id`
 INNER JOIN `user-region` ON `advert`.`advert_place_region` = `user-region`.`id`
 INNER JOIN `user-city` ON `advert`.`advert_place_city` = `user-city`.`id`
 INNER JOIN `category` ON `advert`.`advert_category` = `category`.`id`
 ORDER BY `advert`.`advert_create_date` DESC

Чтобы связи через JOIN большинства таблиц были уже только с теми 80 записями, которые нужны, а не со всей выборкой до LIMIT.

Answer 2

У вас все джойны это связи один к одному. То есть вы выбираете рекламные объявления и джойны нужны только для того чтобы выбрать данные по айдишкам. Кроме пользователя. Для него есть условие, что он активный.

Если идти по объявлениям страны РФ, отсортированным по дате добавления, то можно было бы выбрать только 80 строк. Если бы у бд был составной индекс (advert_place_country, advert_create_date), то она бы могла использовать его.

Кстати, обратите внимание, что индексы по булевым полям обычно мало эффективны. Кажется нет смысла в индексе по advert_active и advert_payment. Можно приделать их к составным индексам для каких-нибудь запросов, но по отдельности они ничего не дают.

UPD:

Экспериментируя, обнаружил, что mysql не умет оптимизировать (возможно только в моём случае) сортировку при джойнах. Он пытается сперва сджойнить и только потом применить сортировку к результату объединения.

В этом случае подзапрос оказывается гораздо шустрее.

select *
from `advert`
where
    `advert_active` = 1 
    and `advert_payment` = 1 
    and `advert_place_country` = 1
    and (select `user`.`active` from `user` where `user`.`id` = `advert`.`advert_id_user`) = true
order by `advert_create_date` desc
limit 0, 80

Таким образом можно получить id нужных записей и сделать второй запрос на получение данных уже с джойнами (или использовать решение Майка).