title: Mysql Correlated Subquery vs Join performance
body: <p>I've always thought that Joins were faster than Subqueries. However for a very simple query in a small dataset the Join is returning in 1.0s whereas the Correlated-Subquery returns in 0.001s. Seems like something is wrong. I note that both queries are using the correct (appallingly named) indexes. Over 1 sec seems excessive for the Join. Any ideas?</p>
  
  <p>Please compare these two queries with their Explain plans:</p>
  
  <p>a) Using a Join</p>
  
  <pre><code>select user.id, user.username, 
  count(distinct bet_placed.id) as bets_placed,
  count(distinct bet_won.id) as bets_won,
  count(distinct bets_involved.id) as bets_involved
  from user 
  left join bet as bet_placed on bet_placed.user_placed = user.id 
  left join bet as bet_won on bet_won.user_won = user.id 
  left join bet_accepters as bets_involved on bets_involved.user = user.id 
  group by user.id
  </code></pre>
  
  <p>Explain plan:</p>
  
  <pre><code>id  select_type table   type    possible_keys   key key_len ref rows    filtered    Extra
  1   SIMPLE  user    index   PRIMARY PRIMARY 4   NULL    86  100.00  NULL
  1   SIMPLE  bet_placed  ref fk_bet_user1_idx    fk_bet_user1_idx    4   xxx.user.id 6   100.00  "Using index"
  1   SIMPLE  bet_won ref user_won_idx    user_won_idx    5   xxx.user.id 8   100.00  "Using index"
  1   SIMPLE  bets_involved   ref FK_user_idx FK_user_idx 4   xxx.user.id 8   100.00  "Using index"
  </code></pre>
  
  <p>Average response time: 1.0 secs</p>
  
  <p>b) Using a Correlated-Subquery</p>
  
  <pre><code>select user.id, user.username, 
  (select COALESCE(count(bet.id), 0) from bet where bet.user_placed = user.id) as bets_placed,
  (select COALESCE(count(bet.id), 0) from bet where bet.user_won = user.id) as bets_won,
  (select COALESCE(count(bet_accepters.id), 0) from bet_accepters where bet_accepters.user = user.id) as bets_involved
  from user;
  </code></pre>
  
  <p>Explain plan:</p>
  
  <pre><code>id  select_type table   type    possible_keys   key key_len ref rows    filtered    Extra
  1   PRIMARY user    ALL NULL    NULL    NULL    NULL    86  100.00  NULL
  4   "DEPENDENT SUBQUERY"    bet_accepters   ref FK_user_idx FK_user_idx 4   xxx.user.id 8   100.00  "Using index"
  3   "DEPENDENT SUBQUERY"    bet ref user_won_idx    user_won_idx    5   xxx.user.id 8   100.00  "Using index"
  2   "DEPENDENT SUBQUERY"    bet ref fk_bet_user1_idx    fk_bet_user1_idx    4   xxx.user.id 6   100.00  "Using index"
  </code></pre>
  
  <p>Average response time: 0.001 secs</p>