Very slow query, what can I do to improve?

Question

I have a problem with this query which was auto generated from the backend framework I am using. The query is slow and causing issues with my script.

UPDATE: After following @The Impaler's answer, the problem is fixed for my initial query, the execution time went from 30s to 300ms.

The only problem is after adding a few extra conditions to my WHERE clause, the execution time increases back to 28s-32s.

OLD QUERY:

SELECT `growthservice_growthserviceaccountdata`.`id`, `growthservice_growthserviceaccountdata`.`username`, 
   `growthservice_growthserviceaccountdata`.`name`, `growthservice_growthserviceaccountdata`.`bio`, 
   `growthservice_growthserviceaccountdata`.`avatar`, `growthservice_growthserviceaccountdata`.`language`, 
   `growthservice_growthserviceaccountdata`.`gender`, `growthservice_growthserviceaccountdata`.`follower_count`,
   `growthservice_growthserviceaccountdata`.`following_count`, `growthservice_growthserviceaccountdata`.`like_count`, 
   `growthservice_growthserviceaccountdata`.`post_count`, `growthservice_growthserviceaccountdata`.`is_private`, 
   `growthservice_growthserviceaccountdata`.`is_business`, `growthservice_growthserviceaccountdata`.`is_verified`, 
   `growthservice_growthserviceaccountdata`.`is_fetched`, `growthservice_growthserviceaccountdata`.`created_date`, 
   `growthservice_growthserviceaccountdata`.`updated_date` 
FROM `growthservice_growthserviceaccountdata` 
INNER JOIN `growthservice_growthservicerelationdata` 
ON (`growthservice_growthserviceaccountdata`.`id` = `growthservice_growthservicerelationdata`.`account_id`) 
WHERE (
    `growthservice_growthservicerelationdata`.`source_id` = 6812397029810258950 
    AND NOT (`growthservice_growthserviceaccountdata`.`id` IN (
        SELECT U0.`subject_id` AS Col1 FROM `growthservice_log` U0 
        WHERE (U0.`account_id` = 6570863662218543109 AND U0.`action` = 'LIKE' AND U0.`subject_type` = 'USER')
        )
        )
        ) LIMIT 55;

NEW QUERY

SELECT `growthservice_growthserviceaccountdata`.`id`, `growthservice_growthserviceaccountdata`.`username`, 
   `growthservice_growthserviceaccountdata`.`name`, `growthservice_growthserviceaccountdata`.`bio`, 
   `growthservice_growthserviceaccountdata`.`avatar`, `growthservice_growthserviceaccountdata`.`language`, 
   `growthservice_growthserviceaccountdata`.`gender`, `growthservice_growthserviceaccountdata`.`follower_count`,
   `growthservice_growthserviceaccountdata`.`following_count`, `growthservice_growthserviceaccountdata`.`like_count`, 
   `growthservice_growthserviceaccountdata`.`post_count`, `growthservice_growthserviceaccountdata`.`is_private`, 
   `growthservice_growthserviceaccountdata`.`is_business`, `growthservice_growthserviceaccountdata`.`is_verified`, 
   `growthservice_growthserviceaccountdata`.`is_fetched`, `growthservice_growthserviceaccountdata`.`created_date`, 
   `growthservice_growthserviceaccountdata`.`updated_date` 
FROM `growthservice_growthserviceaccountdata` 
INNER JOIN `growthservice_growthservicerelationdata` 
ON (`growthservice_growthserviceaccountdata`.`id` = `growthservice_growthservicerelationdata`.`account_id`) 
WHERE (
    `growthservice_growthservicerelationdata`.`source_id` = 6812397029810258950 
    AND `growthservice_growthserviceaccountdata`.`following_count` >= 30 
    AND `growthservice_growthserviceaccountdata`.`follower_count` >= 10 
    AND NOT (`growthservice_growthserviceaccountdata`.`username` LIKE BINARY '%user%') 
    AND NOT (`growthservice_growthserviceaccountdata`.`id` IN (
        SELECT U0.`subject_id` AS Col1 FROM `growthservice_log` U0 
        WHERE (U0.`account_id` = 6570863662218543109 AND U0.`action` = 'LIKE' AND U0.`subject_type` = 'USER')
        )
        )
        ) 
ORDER BY `growthservice_growthserviceaccountdata`.`created_date` DESC
LIMIT 55;

Edited query to show easier readability of table aliases vs long names

SELECT 
            gsad.id, 
            gsad.username,
            gsad.name,
            gsad.bio,
            gsad.avatar,
            gsad.`language`,
            gsad.gender, 
            gsad.follower_count,
            gsad.following_count, 
            gsad.like_count,
            gsad.post_count, 
            gsad.is_private, 
            gsad.is_business, 
            gsad.is_verified,
            gsad.is_fetched, 
            gsad.created_date,
            gsad.updated_date 
        FROM
            growthservice_growthserviceaccountdata gsad
                INNER JOIN growthservice_growthservicerelationdata gsrd
                    ON sad.id = gsrd.account_id
        WHERE 
                gsrd.source_id = 6812397029810258950
            AND sad.following_count >= 30
            AND sad.follower_count >= 10 
            AND NOT sad.username LIKE BINARY '%user%'
            AND NOT sad.id IN ( SELECT U0.subject_id AS Col1 
                                    FROM growthservice_log U0
                                    WHERE (U0.account_id = 6570863662218543109 
                                        AND U0.action = 'LIKE' 
                                        AND U0.subject_type = 'USER') 
                            )
        ORDER BY 
            sad.created_date DESC
        LIMIT 55;






The old EXPLAIN output is:

    | id | select_type    

| table                                   | partitions | type   | possible_keys                                                                                                                                     | key                                                          | key_len | ref                                                                    | rows | filtered | Extra       |
|  1 | PRIMARY            | growthservice_growthservicerelationdata | NULL       | ref    | growthservice_growth_account_id_93684974_fk_growthser,growthservice_growth_source_id_86fb3471_fk_growthser                                        | growthservice_growth_source_id_86fb3471_fk_growthser         | 8       | const                                                                  | 5741 |   100.00 | Using where |
|  1 | PRIMARY            | growthservice_growthserviceaccountdata  | NULL       | eq_ref | PRIMARY,follower_count,following_count                                                                                                            | PRIMARY                                                      | 8       | app.growthservice_growthservicerelationdata.account_id                 | 1 |    22.22 | Using where |
|  2 | DEPENDENT SUBQUERY | U0                                      | NULL       | ref    | growthservice_log_account_id_ac95df3e_fk_accounts_account_id,growthservice_log_action_45cfd84b,growthservice_log_subject_id_17399893,subject_type | growthservice_log_account_id_ac95df3e_fk_accounts_account_id | 8       | const                                                                  | 2822 |     2.50 | Using where |

The new EXPLAIN output is:

| id | select_type        | table                                   | partitions | type   | possible_keys                                                                                              | key                                                  | key_len | ref                                                                    | rows | filtered | Extra       
|  1 | PRIMARY            | growthservice_growthservicerelationdata | NULL       | ref    | growthservice_growth_account_id_93684974_fk_growthser,growthservice_growth_source_id_86fb3471_fk_growthser | growthservice_growth_source_id_86fb3471_fk_growthser | 8       | const                                                                  | 5741 |   100.00 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY            | growthservice_growthserviceaccountdata  | NULL       | eq_ref | PRIMARY,follower_count,following_count,ix1                                                                 | PRIMARY                                              | 8       | app.growthservice_growthservicerelationdata.account_id |    1 |    25.00 | Using where
|  2 | DEPENDENT SUBQUERY | U0                                      | NULL       | ref    | growthservice_log_action_45cfd84b,growthservice_log_subject_id_17399893,subject_type,ix1                   | ix1                                                  | 1612    | const,const,const                                                      | 2564 |    10.00 | Using where

The query is taking on average 30 seconds.

The table growthservice_growthserviceaccountdata has 7 million rows.

The table growthservice_growthservicerelationdata has 7 million rows.

The table growthservice_log has 150 thousand rows.

I already have single column indexes on all the fields being filtered in the where clause.

What exactly can be slowing down the query, and what can I do to fix it?

A very strange thing is that if I remove the NOT IN, from the WHERE clause, the query is executed in only 300 ms, instead of 30 s.

user_6436191 · Answer

Single column indexes won't help the subquery, since it has a three-column equality predicate. To improve the performance of the subquery you can add the index:

create index ix1 on `growthservice_log` (`account_id`, `action`, `subject_type`);

The rest of the query seems pretty straightforward, since you are limiting to 55 rows only, and there's no sorting operation that forces the engine to read a massive number of rows.

Something that can be a problem is that the engine is considering the subquery as a "dependent subquery". If possible it could be useful to to rephrase this query to avoid any correlation.

user_1766831 · Answer

For the first variant, try changing this:

              AND  NOT (a.`id` IN (
                        SELECT  U0.`subject_id` AS Col1
                            FROM  `growthservice_log` U0
                            WHERE  (U0.`account_id` = 6570863662218543109
                               AND  U0.`action` = 'LIKE'
                               AND  U0.`subject_type` = 'USER') ) )

to

     AND NOT EXISTS ( SELECT 1
           FROM growthservice_log AS U0
           WHERE  U0.`account_id` = 6570863662218543109
             AND  U0.`action` = 'LIKE'
             AND  U0.`subject_type` = 'USER'
             AND  U0.subject_cell = a.`id` )

and have

U0:  (subject_type, action, account_id, subject_id)  -- The order does not matter
r:   (source_id, account_id)  -- In this order

I already have single column indexes on all the fields being filtered in the where clause.

A common novice error. "Composite indexes" (as above) are sometimes much better than single-column indexes.

Without an ORDER BY, LIMIT is free to give you random rows.

Second query...

This is quite inefficient:

NOT sad.username LIKE BINARY '%user%'

What is the intent?

For a, one of these might be used:

INDEX(follower_count),
INDEX(following_count)

Another thing to try is moving the NOT IN (or NOT EXISTS) from the WHERE clause to the HAVING clause. If that fails to improve the speed as much as removing it did, then we can talk about a subquery:

SELECT *
    FROM (( SELECT ... all of the query but the NOT IN ...))
    WHERE NOT EXISTS ( ... )
    ORDER BY ...;   -- Yes, repeated

Caveat: You may get only 54 rows, but maybe that will be "good enough" and well worth the speedup?

Very slow query, what can I do to improve?

2 Answers