mysql - 加速mysql内部查询

Question

我目前有:

SELECT ads, id as mid, (SELECT IFNULL(SUM(amount),0) FROM trans WHERE paidout=0 AND user_id=mid) as amount FROM accounts

我如何执行内部求和查询，然后遍历它并以相同的方式将余额放在一起？

Answer 1

子查询将对返回的每一行执行。如果将其转换为 LEFT JOIN，它将执行一次并且应该会快得多。

SELECT
  ads,
  id AS mid,
  COALESCE(amount, 0) AS amount
FROM
  accounts
  LEFT JOIN (
    /* Subquery returns SUM(amount) per user_id */
    /* LEFT JOIN in case there are no matching records */
    SELECT user_id, SUM(amount) AS amount
    FROM trans
    WHERE paidout = 0  
    GROUP BY user_id
  ) amt ON accounts.id = amt.user_id

SUM() 也可以在您的情况下在外部查询中完成，对所有其他列应用 GROUP BY。

SELECT 
  accounts.ads, 
  accounts.id AS mid
  COALESCE(SUM(amount), 0) AS amount
FROM
  accounts
  LEFT JOIN trans ON accounts.id = trans.user_id
WHERE trans.paidout = 0
GROUP BY
  accounts.ads,
  mid