CODEWITHSUNDEEP
phpmysql
John Cliven
John Cliven

Reputation: 1231

MySQL query response taking too long

I'm currently doing some MySQL queries on a large database which is taking extremely long to reply (10 mins each time). I understand the query is complex, but I really do need the time to be rapidly cut as it is halting all other processes while the script (PHP) waits for the response.

Here is the current query (still new to MySQL so I'm sure there's much to be improved) :

SELECT DISTINCT username FROM `saved_users`
    WHERE referral_from IN(SELECT DISTINCT `referral`
                           FROM accounts 
                           WHERE owner = '{$owner}'
                           AND `use` = '1') 
    AND username NOT IN(SELECT username FROM `signup_history` 
                        WHERE owner = '{$owner}') 
    ORDER BY date_added DESC, 
             user_type = 'mod' DESC, 
             scrape_type = DESC 
    LIMIT {$limit}

Thanks to all suggestions.

EDIT: EXPLAIN returned this:

1 PRIMARY saved_users ALL NULL NULL NULL NULL 49101 Using where; Using temporary; Using filesort 
3 DEPENDENT SUBQUERY signup_history ALL NULL NULL NULL NULL 59229 Using where 
2 DEPENDENT SUBQUERY accounts ALL NULL NULL NULL NULL 38 Using where; Using temporary

Upvotes: 2

Views: 805

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I am guessing that username is unique in users. Also, it is often better to use EXISTS/NOT EXISTS rather than IN/NOT IN. So, I would start by rewriting the query as:

SELECT su.username
FROM saved_users su
WHERE EXISTS (SELECT 1
              FROM accounts
              WHERE r.referral = su.referral_from AND
                    owner = '{$owner}' AND
                    `use` = '1'
             ) AND
     NOT EXISTS (SELECT 1 
                 FROM `signup_history` sh
                 WHERE su.username = sh.username AND
                       sh.owner = '{$owner}'
                ) 
ORDER BY date_added DESC, 
         user_type = 'mod' DESC, 
         scrape_type DESC 
LIMIT {$limit} ;

For this query, I would suggest the following indexes: users(date_added, user_type, scrape_type, user_name), referrals(referral, owner, use), and signup_history(username, owner).

Upvotes: 1

Related Questions