Как оптимизировать sql запрос с несколькими Join-ами в ActiveQuery Yii2?

Question

[Артур Косиков]

Запрос:

SELECT DISTINCT `announcements`.* FROM `announcements` 
LEFT JOIN `ann_categories` ON `announcements`.`ann_cat_id` = `ann_categories`.`id` 
LEFT JOIN `ann_subcategories` ON `announcements`.`ann_sub_cat_id` = `ann_subcategories`.`id` 
LEFT JOIN `ann_features_values` ON `announcements`.`id` = `ann_features_values`.`ann_id` 
WHERE `disabled` = 0 
ORDER BY `date_added` DESC 
LIMIT 15

Время выполнения - более 3-х секунд.

Explain показывает:

+----+-------------+---------------------+--------+--------------------------------------+--------------------------------------+---------+--------------------------------------------+-------+----------------------------------------------+
| id | select_type | table               | type   | possible_keys                        | key                                  | key_len | ref                                        | rows  | Extra                                        |
+----+-------------+---------------------+--------+--------------------------------------+--------------------------------------+---------+--------------------------------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | announcements       | ref    | disabled,date_added_disabled         | disabled                             | 1       | const                                      | 50390 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | ann_categories      | eq_ref | PRIMARY                              | PRIMARY                              | 2       | torgobmen_new.announcements.ann_cat_id     |     1 | Using index; Distinct                        |
|  1 | SIMPLE      | ann_subcategories   | eq_ref | PRIMARY                              | PRIMARY                              | 2       | torgobmen_new.announcements.ann_sub_cat_id |     1 | Using index; Distinct                        |
|  1 | SIMPLE      | ann_features_values | ref    | FK_ann_features_values_announcements | FK_ann_features_values_announcements | 5       | torgobmen_new.announcements.id             |     4 | Using index; Distinct                        |
+----+-------------+---------------------+--------+--------------------------------------+--------------------------------------+---------+--------------------------------------------+-------+----------------------------------------------+
4 rows in set (0.00 sec)

Запрос этот сгенерировал Yii2 и непонятен вообще смысл всех джойнов в данном запросе...

Код в контроллере (использован ActiveQuery):

$announcements = Announcements::find()
->joinWith('annCat')
->joinWith('annSubCat')
->joinWith('annFeaturesValues')
->where('`disabled` = 0');
 ->orderBy(['date_added' => SORT_DESC])
->limit(15)->distinct()->asArray()->all();

В результате я получаю массив $announcements в котором есть данные из связанных таблиц. (annCat, annSubCat... - это связи, которые есть в моделях). Все работает "как надо", но 3 секунды на выполнение - это очень много...

Comments

[Александр]

Для начала указать поля, которые нужно выводить через запятую, без использования маски

[Melkij]

поле disabled по которому фильтруете - оно в какой таблице вообще? А date_added, по которому сортируете? Предлагаете угадать?

[Артур Косиков]

Melkij, disabled и date_added - в таблице announcements

[Артур Косиков]

Александр, перечисление полей вместо маски ( * ) не ускорило...

[Sayonji]

Индекс (`disabled`, `date_added`) не помогает?

[Артур Косиков]

Sayonji, не помогает. Вот эксплайн:

+----+-------------+---------------------+--------+--------------------------------------+--------------------------------------+---------+--------------------------------------------+-------+------------------------------+
| id | select_type | table               | type   | possible_keys                        | key                                  | key_len | ref                                        | rows  | Extra                        |
+----+-------------+---------------------+--------+--------------------------------------+--------------------------------------+---------+--------------------------------------------+-------+------------------------------+
|  1 | SIMPLE      | announcements       | ref    | disabled,date_added_disabled         | date_added_disabled                  | 1       | const                                      | 45651 | Using where; Using temporary |
|  1 | SIMPLE      | ann_categories      | eq_ref | PRIMARY                              | PRIMARY                              | 2       | torgobmen_new.announcements.ann_cat_id     |     1 |                              |
|  1 | SIMPLE      | ann_subcategories   | eq_ref | PRIMARY                              | PRIMARY                              | 2       | torgobmen_new.announcements.ann_sub_cat_id |     1 |                              |
|  1 | SIMPLE      | ann_features_values | ref    | FK_ann_features_values_announcements | FK_ann_features_values_announcements | 5       | torgobmen_new.announcements.id             |     4 |                              |
+----+-------------+---------------------+--------+--------------------------------------+--------------------------------------+---------+--------------------------------------------+-------+------------------------------+
4 rows in set (0.00 sec)

Используется этот составной индекс (date_added_disabled) - но все-равно Using temporary

[Sayonji]

Артур Косиков, Я предлагаю не такой, а наоборот поля поставить.

[Артур Косиков]

Sayonji, пробовал и так и так...

[Артур Косиков]

Не понятно, зачем он создает временную таблицу, если и WHERE и ORDER BY и GROUP BY - по полям основной таблицы... Если убрать Join-ы - все ОК. Но они нужны!

[Sayonji]

Потому что у вас последний джоин не по первичному ключу. Из-за него проблемы. Может вам подойдёт переписать запрос на

SELECT ... FROM ann_features_values LEFT JOIN announcements ...ann_id=id... и дальше как у вас

?

[Артур Косиков]

Sayonji, Да, последний джойн не по первичному ключу. Если переписать так запрос - ничего не отдает :(

Answer 1

[zzamzam]

Using temporary; Using filesort

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

Comments

[Артур Косиков]

Есть индексы и для disabled, и для date_added, и даже составной по ним. Но я не виду, чтоб он использовал key date_added ?

[zzamzam]

Ну конечно! Сразу не обратил внимания

SELECT DISTINCT `announcements`.*

Это же вы получается выбираете уникальные строки не по какому-то индексированному полю, а по целой строке со всеми данными из announcements. Само собой приходится их все перебирать, чтобы отсортировать.

Если нужные все данные из всех таблиц, то надо-таки делать GROUP BY по определённому проиндексированному полю, повторений которого вы хотите избежать

Типа того:

SELECT `announcements`.* FROM `announcements`
LEFT JOIN `ann_categories` ON `announcements`.`ann_cat_id` = `ann_categories`.`id`
LEFT JOIN `ann_subcategories` ON `announcements`.`ann_sub_cat_id` = `ann_subcategories`.`id`
LEFT JOIN `ann_features_values` ON `announcements`.`id` = `ann_features_values`.`ann_id`
WHERE `disabled` = 0
GROUP BY announcements.title
ORDER BY `date_added` DESC
LIMIT 15

Answer 2

[Артур Косиков]

Работает супер-быстро, если убрать из запроса DISTINCT.
Но получаются сплошные дубли...
Использование вместо него "GROUP BY" - опять задействует Using temporary

Answer 3

[Melkij]

disabled и date_added - в таблице announcements

Так нафига вам вообще любой из указанных join'ов равно как и distinct?!
Вы из них абсолютно никак не читаете и не используете. Так зачем? Просто размножить строки и героически их склеить обратно distinct'ом?

SELECT `announcements`.* FROM `announcements` 
WHERE `disabled` = 0 
ORDER BY `date_added` DESC 
LIMIT 15

Comments

[Артур Косиков]

Так мне нужны поля из других таблиц (там связи)

[Melkij]

Значит поправьте запрос в вопросе так, чтобы было видно, какие данные вы получить хотите и где они лежат. Я привёл полный эквивалент запросу в вопросе.

[Артур Косиков]

Melkij, Вы абсолютно правы. Результат выполнения будет идентичен. Джойны здесь вообще не имеют смысла... Но тогда вопрос перетекает в ветку "Yii2" (напишу подробности в основной ветке вопроса...)

[Melkij]

У меня нет настроения ковырять очередную глупость очередного orm. Возможно вам не нужны в query builder ни джойны ни distinct, а связанные данные подгружаются отдельно за кадром через известную "N+1 query problem".