How to optimize sql query with multiple Join-s in Yii2 ActiveQuery?

Question

How to optimize sql query with multiple Join-s in Yii2 ActiveQuery?

Query:

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

The run time is more than 3 seconds.

The Explain shows:

+----+-------------+---------------------+--------+--------------------------------------+--------------------------------------+---------+--------------------------------------------+-------+----------------------------------------------+
| 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)

This request generated Yii2 and do not understand the meaning of all joynow in this query...

The code in the controller (used ActiveQuery):

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

As a result, I get the array $announcements which has data from related tables. (annCat, annSubCat... links that are in the models). Everything is working "right", but 3 seconds to execute - a lot...

oren.Li asked June 10th 19 at 17:00

Related questions

More answers about "How to optimize sql query with multiple Join-s in Yii2 ActiveQuery?"

3 answers

elena_Decko answered on · Answer 1 · 2019-06-10T15:02:01.361Z

elena_Decko answered on June 10th 19 at 17:02

Using temporary; Using filesort

suggests that a temporary table is created and sorted by the bulkhead each field.
For disabled and date_added have indexes?

There are indexes for the disabled, and for date_added and even compound them. But I don't mind that he used key date_added ? - oren.Li commented on June 10th 19 at 17:05

Well, of course! Just do not pay attention

SELECT DISTINCT `announcements`.*

It turns out you choose a unique row not by any indexed field, and a string with all the data from announcements. Of course have them all sort out to sort.

If you need all data from all tables, you need to do GROUP BY for a particular indexed field of repetitions you want to avoid

Like that:

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

- elena_Decko commented on June 10th 19 at 17:08

oren.Li answered on · Answer 2 · 2019-06-10T15:04:01.361Z

Works super-fast if you remove DISTINCT from the query.
But get a solid doubles...
Instead use "GROUP BY" - again, will involve Using temporary

Ignacio_Romaguera answered on · Answer 3 · 2019-06-10T15:06:01.361Z

Ignacio_Romaguera answered on June 10th 19 at 17:06

disabled and date_added in table announcements

So what for you to do any of the joins s as well as distinct?!
You have absolutely not read and do not use. So why? Just multiply row and heroically to glue them back distinct'ohms?

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

So I need fields from other tables (there) - oren.Li commented on June 10th 19 at 17:09

Then correct the query in question to see what type of data you want to and where they lie. I gave a full equivalent of the query in question. - elena_Decko commented on June 10th 19 at 17:12

You are absolutely right. The execution result will be identical. Joiny here have no sense at all... But then the question shifts to thread "Yii2" (will write details in the main thread question...) - oren.Li commented on June 10th 19 at 17:15

I am in no mood to pick another stupid another orm. You may not need in the query builder no joiny nor distinct, but related data is loaded separately by frame through the famous "N+1 query problem". - Ignacio_Romaguera commented on June 10th 19 at 17:18