MYSQL 选择计数、求和、内连接和分组依据

Question

SELECT COUNT(s.fid) AS tcount, SUM(t.size) AS tsize, s.uid 
FROM xbt_files_users AS s INNER JOIN torrents AS t 
ON s.fid = t.id 
WHERE s.active = 1 AND s.mtime > '.$time.' AND s.remaining = 0 AND t.size >= 52428800 
GROUP BY s.uid

我的这个查询有一些问题，在繁忙的情况下，有时需要超过 20 秒......xbt_files_users 的索引为:ARM(事件、剩余、mtime)、uid、(fid/uid - 唯一)torrent 有大小索引，id 主要它使用:

Using where; Using temporary; Using filesort for xbt_files_users

和

Using where for torrents

有什么方法可以改进这个查询吗？

Answer 1

这是您的查询:

SELECT COUNT(s.fid) AS tcount, SUM(t.size) AS tsize, s.uid 
FROM xbt_files_users s INNER JOIN
     torrents t 
     ON s.fid = t.id 
WHERE s.active = 1 AND s.mtime > '.$time.' AND s.remaining = 0 AND t.size >= 52428800 
GROUP BY s.uid

这些查询的最佳索引是xbt_files_users(active，remaining，mtime，fid，uid)和torrents(id，size)。我将从这些索引开始。