title: MySQL : Avoid Temporary/Filesort Caused by GROUP BY Clause
body: <p>I've got a fairly simple query that seeks to display the number of email addresses that are subscribed along with the number unsubscribed, grouped by client.</p>
  
  <p>The query:</p>
  
  <pre><code>SELECT
      client_id,
      COUNT(CASE WHEN subscribed = 1 THEN subscribed END) AS subs,
      COUNT(CASE WHEN subscribed = 0 THEN subscribed END) AS unsubs
  FROM
      contacts_emailAddresses
  LEFT JOIN contacts ON contacts.id = contacts_emailAddresses.contact_id
  GROUP BY
      client_id
  </code></pre>
  
  <p>Schema of relevant tables follows. contacts_emailAddresses is a junction table between contacts (which has the client_id) and emailAddresses (which is not actually used in this query).</p>
  
  <pre><code>CREATE TABLE `contacts` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `firstname` varchar(255) NOT NULL DEFAULT '',
    `middlename` varchar(255) NOT NULL DEFAULT '',
    `lastname` varchar(255) NOT NULL DEFAULT '',
    `gender` varchar(5) DEFAULT NULL,
    `client_id` mediumint(10) unsigned DEFAULT NULL,
    `datasource` varchar(10) DEFAULT NULL,
    `external_id` int(10) unsigned DEFAULT NULL,
    `created` timestamp NULL DEFAULT NULL,
    `trash` tinyint(1) NOT NULL DEFAULT '0',
    `updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `client_id` (`client_id`),
    KEY `external_id combo` (`client_id`,`datasource`,`external_id`),
    KEY `trash` (`trash`),
    KEY `lastname` (`lastname`),
    KEY `firstname` (`firstname`),
    CONSTRAINT `contacts_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=14742974 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
  
  CREATE TABLE `contacts_emailAddresses` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `contact_id` int(10) unsigned NOT NULL,
    `emailAddress_id` int(11) unsigned DEFAULT NULL,
    `primary` tinyint(1) unsigned NOT NULL DEFAULT '0',
    `subscribed` tinyint(1) unsigned NOT NULL DEFAULT '1',
    `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `contact_id` (`contact_id`),
    KEY `subscribed` (`subscribed`),
    KEY `combo` (`contact_id`,`emailAddress_id`) USING BTREE,
    KEY `emailAddress_id` (`emailAddress_id`) USING BTREE,
    CONSTRAINT `contacts_emailAddresses_ibfk_1` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`),
    CONSTRAINT `contacts_emailAddresses_ibfk_2` FOREIGN KEY (`emailAddress_id`) REFERENCES `emailAddresses` (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=24700918 DEFAULT CHARSET=utf8
  </code></pre>
  
  <p>Here's the EXPLAIN:</p>
  
  <pre><code>+----+-------------+-------------------------+--------+---------------+---------+---------+-------------------------------------------+----------+---------------------------------+
  | id | select_type | table                   | type   | possible_keys | key     | key_len | ref                                       | rows     | Extra                           |
  +----+-------------+-------------------------+--------+---------------+---------+---------+-------------------------------------------+----------+---------------------------------+
  | 1  | SIMPLE      | contacts_emailAddresses | ALL    | NULL          | NULL    | NULL    | NULL                                      | 10176639 | Using temporary; Using filesort |
  | 1  | SIMPLE      | contacts                | eq_ref | PRIMARY       | PRIMARY | 4       | icarus.contacts_emailAddresses.contact_id | 1        |                                 |
  +----+-------------+-------------------------+--------+---------------+---------+---------+-------------------------------------------+----------+---------------------------------+
  2 rows in set (0.08 sec)
  </code></pre>
  
  <p>The problem here clearly is the GROUP BY clause, as I can remove the JOIN (and the items that depend on it) and the performance still is terrible (40+ seconds).  There are 10m records in contacts_emailAddresses, 12m-some records in contacts, and 10–15 client records for the grouping.</p>
  
  <p>From the <a href="http://dev.mysql.com/doc/refman/5.6/en/internal-temporary-tables.html" rel="noreferrer">doc</a>:</p>
  
  <blockquote>
    <p>Temporary tables can be created under conditions such as these:</p>
    
    <p>If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.</p>
    
    <p>DISTINCT combined with ORDER BY may require a temporary table.</p>
    
    <p>If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table, unless the query also contains elements (described later) that require on-disk storage.</p>
  </blockquote>
  
  <p>I'm obviously not combining the GROUP BY with an ORDER BY, and I have tried multiple things to ensure that the GROUP BY is on a column that should be properly placed in the join queue (including rewriting the query to put contacts in the FROM and instead join to contacts_emailAddresses), all to no avail.</p>
  
  <p>Any suggestions for performance tuning would be much appreciated!</p>