mysql - 使用 JOIN 获取大量记录

Question

我有一个查询，用于使用 JOIN 从多个表中获取记录。

SELECT c.id           AS contestant_id,
       c.created_date,
       c.name         AS contestant_name,
       counter.total  AS score,
       c.email
  FROM submission AS s,

       (SELECT ans.id AS ans_id, sub.contestant_id, count(sub.id) AS total
          FROM submission AS sub
          JOIN (SELECT id, is_true FROM answer) AS ans
         WHERE sub.answer_id = ans.id
           AND ans.is_true = 1
         GROUP BY sub.contestant_id) AS counter

  JOIN (SELECT id, name, email, type, created_date
          FROM contestant
         WHERE contest_type = 1
           AND submission_status = 1) AS c

 WHERE counter.contestant_id = c.id
 GROUP BY c.id
 ORDER BY c.created_date DESC

问题是contestant表中的每条记录在submission表中将有30条记录。因此，当我检索到 1000 名或更多参赛者时，服务器就会挂起。

Answer 1

请尝试以下重组查询:

SELECT 
    c.id AS contestant_id, 
    c.created_date, 
    c.name AS contestant_name, 
    counter.total AS score, 
    c.email
FROM 
    (
      SELECT  
        sub.contestant_id, count(sub.id) AS total
      FROM 
        submission AS sub 
        JOIN answer AS ans
            ON sub.answer_id = ans.id AND ans.is_true = 1 
      GROUP BY 
        sub.contestant_id
    )
    AS counter
    JOIN contestant c
      ON c.contest_type = 1 AND c.submission_status = 1 AND c.id = counter.contestant_id
WHERE 
    counter.contestant_id = c.id 
GROUP BY 
    c.id 
ORDER BY 
    c.created_date DESC