我目前正在一个大型数据库上进行一些MySQL查询,这需要非常长的时间才能回复(每次10分钟(。我知道查询很复杂,但我确实需要快速缩短时间,因为在脚本(PHP(等待响应时,它会停止所有其他进程。
以下是当前的查询(对于MySQL来说仍然是新的,所以我相信还有很多需要改进的地方(:
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}
感谢所有的建议。
编辑:EXPLAIN返回此:
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
我猜username
在users
中是唯一的。此外,通常使用EXISTS
/NOT EXISTS
比使用IN
/NOT IN
更好。因此,我首先将查询重写为:
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} ;
对于这个查询,我建议使用以下索引:users(date_added, user_type, scrape_type, user_name)
、referrals(referral, owner, use)
和signup_history(username, owner)
。