Using index, using temporary, using filesort - how to fix this?

Question

I'm working on a event tracking system which uses a handful of lookup tables as well as the primary logging table. In a report I'm writing, an object can be selected to view statistics against. The interface shows all objects in order of decreasing importance (ie, hits).

The schema for the two tables (slightly trimmed down, but you get the gist):

CREATE TABLE IF NOT EXISTS `event_log` (
  `event_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(5) DEFAULT NULL,
  `object_id` int(5) DEFAULT NULL,
  `event_date` datetime DEFAULT NULL,
  PRIMARY KEY (`event_id`),
  KEY `user_id` (`user_id`),
  KEY `object_id` (`object_id`)
);

CREATE TABLE IF NOT EXISTS `lookup_event_objects` (
  `object_id` int(11) NOT NULL AUTO_INCREMENT,
  `object_desc` varchar(255) NOT NULL,
  PRIMARY KEY (`object_id`)
);

The query I'm having trouble with is below. It works fine with my table of ~100 entries, but the EXPLAIN worries me a little.

    explain SELECT 
            el.object_id, 
            leo.object_desc, 
            COUNT(el.object_id) as count_rows
        FROM 
            event_log el 
            LEFT JOIN lookup_event_objects leo ON leo.object_id = el.object_id
        GROUP BY 
            el.object_id
        ORDER BY 
            count_rows DESC,
            leo.object_desc ASC

Returns: Using index; Using temporary; Using filesort

So -- what's wrong with my schema and/or query for MySQL to fall back on temporary and filesort? Or is it as optimized as it can get using ORDER BY?

Solution 1

Well, the doc gives the exact reasons when "Using temporary" will appear:

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.

A quick scan shows that you suffer from #1.

And this blog from 2009 says that "using filesort" means that the sort can't be performed with an index. Since you're ordering by a computed field, that's going to be true, too.

So, that's what's "wrong".

Solution 2

Updated for MySQL 5.7 (src):

The server creates temporary tables under conditions such as these:

• Evaluation of UNION statements, with some exceptions described later.

• Evaluation of some views, such those that use the TEMPTABLE algorithm, UNION, or aggregation.

• Evaluation of derived tables (subqueries in the FROM clause).

• Tables created for subquery or semi-join materialization (see Section 8.2.2, “Optimizing Subqueries, Derived Tables, and View References”).

• Evaluation of statements that contain an ORDER BY clause and a different GROUP BY clause, or for which the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue.

• Evaluation of DISTINCT combined with ORDER BY may require a temporary table.

• For queries that use the SQL_SMALL_RESULT modifier, MySQL uses an in-memory temporary table, unless the query also contains elements (described later) that require on-disk storage.

• To evaluate INSERT ... SELECT statements that select from and insert into the same table, MySQL creates an internal temporary table to hold the rows from the SELECT, then inserts those rows into the target table. See Section 13.2.5.1, “INSERT ... SELECT Syntax”.

• Evaluation of multiple-table UPDATE statements.

• Evaluation of GROUP_CONCAT() or COUNT(DISTINCT) expressions.

Solution 3

These are the following conditions under which temporary tables are created. UNION queries use temporary tables.

Some views require temporary tables, such those evaluated using the TEMPTABLE algorithm, or that use UNION or aggregation.

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.

Follow this link by mysql: http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html

Comments

[a coder]

The maximum number of objects I'd ever return from this query is <300, although I will potentially have millions of rows in the event_log. I confirmed that using temporary and using filesort goes away when I remove ORDER BY. I will try changing this so that I do the ordering (in an array) with the scripting language (PHP). Thanks for your response and links.

[Alain Collins]

It still may be more efficient to do the sorting in MySQL - it already has the data loaded up in a data structure, etc.

[a coder]

It would certainly be cleaner. Thanks for the feedback.

[Oddman]

A good way to solve this is to cache the count on those event_log records. You could do this either via a trigger, or some code in your application. You can then sort by that column with no performance issues. It also means you wouldn't need the join. It's essentially a form of denormalization.