title: Using index, using temporary, using filesort - how to fix this?
body: <p>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).</p>
  
  <p>The schema for the two tables (slightly trimmed down, but you get the gist): </p>
  
  <pre><code>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`)
  );
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>    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
  </code></pre>
  
  <p>Returns: 
  <code>Using index; Using temporary; Using filesort</code></p>
  
  <p>So -- what's wrong with my schema and/or query for MySQL to fall back on <code>temporary</code> and <code>filesort</code>?  Or is it as optimized as it can get using ORDER BY? </p>