使用子查询优化MySQL查询

Question

I am trying to optimize this query below to make it more efficient and stop loading slow. Can someone please show me what I would need to be doing to optimize this for future references.

SELECT * , username AS user, 
(SELECT COUNT(*) FROM pvp WHERE killer = user) AS kills, 
(SELECT COUNT(*) FROM pvp WHERE username = user) AS deaths, 
ROUND(kills / deaths, 2) AS kd FROM stats WHERE username =  "Username"

Answer 1

Try this,

SELECT username AS user, sum(kills) kills,sum(deaths) deaths FROM    
(
 select (SELECT CASE WHEN killer = user THEN 1 ELSE 0 END kills,
  CASE WHEN username = user THEN 1 ELSE 0 END deaths from pvp),
  ROUND(kills / deaths, 2) AS kd FROM stats 
) a
GROUP BY username