title: Optimizing MySQL Join and Group By with intermediate table
body: <p>Simplifying but I have three tables:</p>
  
  <ul>
  <li>users (user_id, team_id)</li>
  <li>results (user_id, result)</li>
  <li>user_signups (user_id, team_id, event_id)</li>
  </ul>
  
  <p>results.user_id is a foreign key.</p>
  
  <p>Tables have large number of rows in. If I do </p>
  
  <pre><code>select sum(result)
  from results
  inner join users on users.id = results.user_id
  group by team_id
  </code></pre>
  
  <p>It is fast. "Explain" has results with 150k rows, users with 1 row.</p>
  
  <p>If I do</p>
  
  <pre><code>select sum(result)
  from results
  inner join user_signups on user_signups.user_id = results.user_id
  where event_id = 1
  group by team_id
  </code></pre>
  
  <p>It is very slow (from 1 second to 14). "Explain" has results with 28 rows, user_signups with 5345 rows.</p>
  
  <p>Things I have tried:</p>
  
  <p>A unique index on event_id and user_id on user_signups.</p>
  
  <p>An index on event_id, user_id, team_id on user_signups.</p>
  
  <p>Rewriting as </p>
  
  <pre><code>select sum(result)
  from results
  inner join (select * from user_signups where event_id = 1) user_signups on user_signups.user_id = results.user_id
  group by team_id
  </code></pre>
  
  <p>Rewriting as </p>
  
  <pre><code>select sum(result)
  from results
  inner join users on users.id = results.user_id
  inner join user_signups on user_signups.user_id = users.id
  where event_id = 1
  group by user_signups.team_id
  </code></pre>
  
  <p>Any other suggestions?</p>