title: Very slow query, what can I do to improve?
body: <p>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.</p>
  <p>UPDATE: After following @The Impaler's answer, the problem is fixed for my initial query, the execution time went from 30s to 300ms.</p>
  <p>The only problem is after adding a few extra conditions to my WHERE clause, the execution time increases back to 28s-32s.</p>
  <p>OLD QUERY:</p>
  <pre><code>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;
  </code></pre>
  <p>NEW QUERY</p>
  <pre><code>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` &gt;= 30 
      AND `growthservice_growthserviceaccountdata`.`follower_count` &gt;= 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;
  </code></pre>
  <p>Edited query to show easier readability of table aliases vs long names</p>
  <pre><code>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 &gt;= 30
              AND sad.follower_count &gt;= 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 |
  </code></pre>
  <p>The new EXPLAIN output is:</p>
  <pre><code>| 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 
  </code></pre>
  <p>The query is taking on average 30 seconds.</p>
  <p>The table growthservice_growthserviceaccountdata has 7 million rows.</p>
  <p>The table growthservice_growthservicerelationdata has 7 million rows.</p>
  <p>The table growthservice_log has 150 thousand rows.</p>
  <p>I already have single column indexes on all the fields being filtered in the where clause.</p>
  <p>What exactly can be slowing down the query, and what can I do to fix it?</p>
  <p>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.</p>