title: Why the performance is affected with a LEFT JOIN and a GROUP BY?
body: <p>I'm not understanding what MySQL (InnoDB) is doing with my queries. I have a query to extract data from two tables and it runs in ~35 ms. If I run the query without the LEFT JOIN, it does it in ~2.5 ms. Even the “equivalent” query to what the LEFT JOIN is doing takes ~0.5 ms. Why?</p>
  
  <p>The “slow” query is the following:</p>
  
  <pre><code>SELECT
      `Assigned`.`id`,
      `Assigned`.`name`,
      (COUNT(`Action`.`id`)) AS `Action__total_actions`
  
  FROM `actions` AS `Action`
  
  LEFT JOIN `users` AS `Assigned` ON (`Assigned`.`id` = `Action`.`user_assigned_id`)
  
  WHERE
      `Action`.`company_id` = 1 AND
      `Action`.`action_date` BETWEEN '2014-12-28 00:00:00' AND '2015-01-28 23:59:59'
  
  GROUP BY `Action`.`user_assigned_id`
  ORDER BY `Assigned`.`name` ASC;
  </code></pre>
  
  <p>And I have one primary index for the table <strong>users</strong> and the next index for table <strong>actions</strong>:</p>
  
  <pre><code>ALTER TABLE `actions` ADD INDEX `actions_report_by_assigned` (`company_id`, `action_date`, `user_assigned_id`);
  </code></pre>
  
  <p>This is when it gets weird. If I “extract” the LEFT JOIN, the index is still working (for both queries), but the next one is 10 times faster:</p>
  
  <pre><code>SELECT
      `Action`.`user_assigned_id`,
      (COUNT(`Action`.`id`)) AS `Action__total_actions`
  
  FROM `actions` AS `Action`
  WHERE
      `Action`.`company_id` = 1 AND
      `Action`.`action_date` BETWEEN '2014-12-28 00:00:00' AND '2015-01-28 23:59:59'
  
  GROUP BY `Action`.`user_assigned_id`
  ORDER BY `Action`.`user_assigned_id`;
  </code></pre>
  
  <p>I think the index is well designed because both queries go through the same total rows that are counting. The EXPLAIN command tells me that the Index it's being used, but it also says in the extra column this: “<em>Using where; Using index; Using temporary; Using filesort</em>” in both queries (besides, one is 10 times faster).</p>
  
  <p>Maybe is the filesort with my LEFT JOIN, because if I remove the GROUP from my first query, it speed up until ~15 ms. Sadly, I can't do that. Am I missing something?</p>
  
  <p>Should I ignore this? What is the best way to solve it?</p>