MySQL : Avoid Temporary/Filesort Caused by GROUP BY Clause

Question

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.

The query:

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

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).

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

Here's the EXPLAIN:

+----+-------------+-------------------------+--------+---------------+---------+---------+-------------------------------------------+----------+---------------------------------+
| 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)

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.

From the doc:

Temporary tables can be created under conditions such as these:

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.

DISTINCT combined with ORDER BY may require a temporary table.

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.

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.

Any suggestions for performance tuning would be much appreciated!

Answer 1

I think the only real shot you have of getting away from a "Using temporary; Using filesort" operation (given the current schema, the current query, and the specified resultset) would be to use correlated subqueries in the SELECT list.

SELECT c.client_id
     , (SELECT IFNULL(SUM(es.subscribed=1),0)
          FROM contacts_emailAddresses es
          JOIN contacts cs
            ON cs.id = es.contact_id
         WHERE cs.client_id = c.client_id
       ) AS subs
     , (SELECT IFNULL(SUM(eu.subscribed=0),0)
          FROM contacts_emailAddresses eu
          JOIN contacts cu
            ON cu.id = eu.contact_id
         WHERE cu.client_id = c.client_id
       ) AS unsubs
  FROM contacts c
 GROUP BY c.client_id

This may run quicker than the original query, or it may not. Those correlated subqueries are going to get run for each returned by the outer query. If that outer query is returning a boatload of rows, that's a whole boatload of subquery executions.

Here's the output from an EXPLAIN:

---

id  select_type        table type  possible_keys                       key        key_len  ref   Extra
--  ------------------ ----- ----- ----------------------------------- ---------- ------- ------ ------------------------
 1  PRIMARY            c     index (NULL)                              client_id  5       (NULL) Using index
 3  DEPENDENT SUBQUERY cu    ref   PRIMARY,client_id,external_id combo client_id  5       func   Using where; Using index
 3  DEPENDENT SUBQUERY eu    ref   contact_id,combo                    contact_id 4       cu.id  Using where
 2  DEPENDENT SUBQUERY cs    ref   PRIMARY,client_id,external_id combo client_id  5       func   Using where; Using index
 2  DEPENDENT SUBQUERY es    ref   contact_id,combo                    contact_id 4       cs.id  Using where

---

For optimum performance of this query, we'd really like to see "Using index" in the Extra column of the explain for the eu and es tables. But to get that, we'd need a suitable index, one with a leading column of contact_id and including the subscribed column. For example:

CREATE INDEX cemail_IX2 ON contacts_emailAddresses (contact_id, subscribed);

With the new index available, EXPLAIN output shows MySQL will use the new index:

---

id  select_type        table type  possible_keys                       key        key_len ref    Extra                     
--  ------------------ ----- ----- ----------------------------------- ---------- ------- ------ ------------------------
 1  PRIMARY            c     index (NULL)                              client_id  5       (NULL) Using index
 3  DEPENDENT SUBQUERY cu    ref   PRIMARY,client_id,external_id combo client_id  5       func   Using where; Using index
 3  DEPENDENT SUBQUERY eu    ref   contact_id,combo,cemail_IX2         cemail_IX2 4       cu.id  Using where; Using index
 2  DEPENDENT SUBQUERY cs    ref   PRIMARY,client_id,external_id combo client_id  5       func   Using where; Using index
 2  DEPENDENT SUBQUERY es    ref   contact_id,combo,cemail_IX2         cemail_IX2 4       cs.id  Using where; Using index

---

NOTES

This is the kind of problem where introducing a little redundancy can improve performance. (Just like we do in a traditional data warehouse.)

For optimum performance, what we'd really like is to have the client_id column available on the contacts_emailAddresses table, without a need to JOINI to the contacts table.

In the current schema, the foreign key relationship to contacts table gets us the client_id (rather, the JOIN operation in the original query is what gets it for us.) If we could avoid that JOIN operation entirely, we could satisfy the query entirely from a single index, using the index to do the aggregation, and avoiding the overhead of the "Using temporary; Using filesort" and JOIN operations...

With the client_id column available, we'd create a covering index like...

... ON contacts_emailAddresses (client_id, subscribed)

Then, we'd have a blazingly fast query...

SELECT e.client_id
     , SUM(e.subscribed=1) AS subs
     , SUM(e.subscribed=0) AS unsubs
  FROM contacts_emailAddresses e
GROUP BY e.client_id

That would get us a "Using index" in the query plan, and the query plan for this resultset just doesn't get any better than that.

But, that would require a change to your scheam, it doesn't really answer your question.

---

---

Without the client_id column, then the best we're likely to do is a query like the one Gordon posted in his answer (though you still need to add the GROUP BY c.client_id to get the specified result.) The index Gordon recommended will be of benefit...

... ON contacts_emailAddresses(contact_id, subscribed)

With that index defined, the standalone index on contact_id is redundant. The new index will be a suitable replacement to support the existing foreign key constraint. (The index on just contact_id could be dropped.)

---

Another approach would be to do the aggregation on the "big" table first, before doing the JOIN, since it's the driving table for the outer join. Actually, since that foreign key column is defined as NOT NULL, and there's a foreign key, it's not really an "outer" join at all.

SELECT c.client_id
     , SUM(s.subs) AS subs
     , SUM(s.unsubs) AS unsubs 
  FROM ( SELECT e.contact_id
              , SUM(e.subscribed=1) AS subs
              , SUM(e.eubscribed=0) AS unsubs
           FROM contacts_emailAddresses e
          GROUP BY e.contact_id
       ) s
 JOIN contacts c
   ON c.id = s.contact_id
GROUP BY c.client_id

Again, we need an index with contact_id as the leading column and including the subscribed column, for best performance. (The plan for s should show "Using index".) Unfortunately, that's still going to materialize a fairly sizable resultset (derived table s) as a temporary MyISAM table, and the MyISAM table isn't going to be indexed.