title: MySQL: Slow queries using "group by" - stuck in "Copying to tmp table"
body: <p>I am working on a "leaderboard" for a web app relating to a sporting event, which reports the top 20 users based on their scores across all their responses to a multiple choice quiz. It also displays the current user's own rank within the leaderboard.</p>
  
  <p>When this application is being load tested, the two queries concerned become very slow, spending a lot of time in "Copying to tmp table" state (up to 20 seconds per query). They do process eventually, but in the meantime hundreds can stack up.</p>
  
  <p>In isolation given a reasonable number of rows in responses table, each query takes about 1 second to execute (25K users, e.g. 200K rows in responses) </p>
  
  <p>I've added some indexes to the tables concerned, particularly for the FK columns and anything used in the where statements. I've also added a covering index for userID,answerID on the responses table.</p>
  
  <p>This is the query for the leaderboard itself</p>
  
  <pre><code>SELECT users.username, sum(questions.points) as score FROM responses
  JOIN answers on responses.answerID = answers.answerID
  JOIN questions on answers.questionID = questions.questionID
  JOIN users on responses.userID = users.userID
  WHERE users.username != '' AND answers.isCorrect  
  GROUP BY users.userID
  ORDER BY score DESC
  LIMIT 20
  </code></pre>
  
  <p>This is the query to get the user's own rank within the results; a separate query gets their score first and then we count how many users have higher scores.</p>
  
  <pre><code>Select count(*) +1 as rank  from (
      SELECT users.username, sum(questions.points) as score
      FROM responses
      JOIN answers on responses.answerID = answers.answerID
      JOIN questions on answers.questionID = questions.questionID
      JOIN users on responses.userID = users.userID
      WHERE users.username != '' AND answers.isCorrect 
      GROUP BY users.userID
      HAVING sum(questions.points) &gt; 2431
      ORDER BY score DESC
  ) as result
  </code></pre>
  
  <p>Simplified schema is </p>
  
  <pre><code>QUESTIONS
  questionID
  question
  points
  
  ANSWERS (multiple choice answers for question)
  answerID
  questionID
  answer
  isCorrect
  
  RESPONSES (the player's choice of answer)
  responseID
  answerID
  userID
  </code></pre>
  
  <p>I think these queries are done in a vaguely sensible way, but I would like to know if there is an obvious better way to do either of these that I have not considered.</p>
  
  <p>Also, does anyone have any thoughts on why these queries stack up in "Copying to tmp table" state and just take so long to process when server is under load? I thought it might be creating them on disk but I see that is a separate state message. I have used EXPLAIN but my feeling is that the temp table is unavoidable with these queries; hence wondering by the "Copying to tmp table" takes so long</p>
  
  <p>Constraints: not shown, users have teamIDs and queries are also filtered by teamID. Also not shown, there are several events and these queries can also be filtered by eventID. Also, not all questions have a correct answer at the time they're answered. Correct answers may be assigned at some future point, but at any rate at the end of the sporting event. The system reports percentages of users choosing each answer. So various ways of storing scores in a more aggregated way have been considered but discarded since they conflict with one or more of these constraints.</p>
  
  <p>Hope this is enough to go on - many thanks</p>