这个查询在数据库较小时运作良好,但现在有数百万行数据时,我意识到应该早些优化它。它正在查看超过600,000行数据并使用了“Using where; Using temporary; Using filesort”(导致执行时间为5-10秒)。它正在使用字段“battle_type”的索引。
SELECT username, SUM( outcome ) AS wins, COUNT( * ) - SUM( outcome ) AS losses
FROM tblBattleHistory
WHERE battle_type = '0' && outcome < '2'
GROUP BY username
ORDER BY wins DESC , losses ASC , username ASC
LIMIT 0 , 50