Optmizing ORDER BY

3 votes

2 answers

168 views

mysql performance order-by query-performance

                          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 	 	eq_ref 	 	 	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             
                        

Asked by John Cliven (131 rep)

Oct 2, 2015, 03:26 PM
Last activity: Jul 29, 2025, 03:08 AM

Optmizing ORDER BY

Related Questions