3

I'm new to MySQL and have written the following query (see below).

PROBLEM: The query returns accurate data but takes about 40 seconds to execute. However, When I remove the ORDER BY part, it takes 0.06sec.

I have indexes on each of the ORDER BY columns and am unsure what else to do. If anybody can rewrite/recommend changes it'd be greatly informative.

CODE:

 SELECT DISTINCT username
    FROM `users`
    WHERE `in_progress` = 0
      AND scraped_from IN
        (SELECT DISTINCT `username`
         FROM source_accounts
         WHERE group_users = 'New Users'
           AND (`type` = 'users' OR `type` = 'both')
           AND `use` = '1')
      AND username NOT IN
        (SELECT user_tofollow
         FROM `follow_history`
         WHERE owner_account = 'admin')
    ORDER BY real_user DESC,
             IF((last_used) IS NULL,'0','1'),
             IF((last_update)>3,'1','0'),
             DATE(last_used),
             IF((user_ratio)>100,'1','0') LIMIT 1000;

EXPLAIN:

1   PRIMARY     scraped_users   ref     idx1,scraped_from,in_progress,username  in_progress     1   const   687025  Using where; Using temporary; Using filesort
1   PRIMARY     <subquery2>     eq_ref  <auto_key>  <auto_key>  767     userstack.users.scraped_from    1   Distinct
2   MATERIALIZED    source_accounts     ref     username,group_users,type,use   group_users     767     const   48  Using index condition; Using where; Distinct
3   DEPENDENT SUBQUERY  follow_history  index_subquery  user_tofollow   user_tofollow   767     func    1   Using where             
  • 5
    I smiled at the "works well, but takes about 40 seconds to execute" but I guess you meant it returns correct results. Please add the SHOW CREATE TABLE table_name; output for the 3 tables and the EXPLAIN SELECT ... output of your query. – ypercubeᵀᴹ Oct 2 '15 at 15:32
  • 1
    From "I have indexes on each of the ORDER BY" I can say that you should learn about composite (multicolumn) indexes. And the subqueries in IN are unfortunatelly not well optimized in most mysql versions. As ypercube says, EXPLAIN to get the query plan. – jkavalik Oct 2 '15 at 15:51
  • Looking into composite indexes. Explain added above ^ – John Cliven Oct 2 '15 at 16:15
  • Can you add the explain for the "fast version" too, to compare it? – jkavalik Oct 2 '15 at 16:28
  • 1
    From the explain it seems the subqueries themselves are actually optimized quite well, the materialized one is good and the dependent one probably uses "not exists" optimization (explain extended ... ; show warning; could tell you for sure). The problem is that the only remaining condition is in_progress = 0 and that is not selective enough. You should rewrite it so at least the first subquery is a join instead. Your order by is unfortunatelly not optimizable to a index scan as it contains some functions/expressions and not "pure" columns. – jkavalik Oct 2 '15 at 18:55
0

The performance problem is not with ORDER BY, itself...

NOT IN ( SELECT ... ) optimizes poorly. This works much better (if it is practical in your cases): JOIN ... ON ... WHERE id IS NULL

If that can't be done, then this may be better: NOT EXISTS ( SELECT * FROM ... )

-3

Make sure you have indexes on the columns you are attempting to order by. Here is a pretty good reference:

https://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

  • It is actually not possible to optimize the ORDER BY clause as it contains some functions. – jkavalik Oct 2 '15 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.