title: GROUP BY query optimization
body: <p>Database is MySQL with MyISAM engine.</p>
  
  <p>Table definition:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS  matches  (
     id  int(11) NOT NULL AUTO_INCREMENT,
     game  int(11) NOT NULL,
     user  int(11) NOT NULL,
     opponent  int(11) NOT NULL,
     tournament  int(11) NOT NULL,
     score  int(11) NOT NULL,
     finish  tinyint(4) NOT NULL,
    PRIMARY KEY ( id ),
    KEY  game  ( game ),
    KEY  user  ( user ),
    KEY  i_gfu ( game , finish , user )
  ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3149047 ;
  </code></pre>
  
  <p>I have set an index on <code>(game, finish, user)</code> but this <code>GROUP BY</code> query still needs 0.4 - 0.6 seconds to run:</p>
  
  <pre><code>SELECT user AS player
       , COUNT( id ) AS times
  FROM matches
  WHERE finish = 1
    AND game = 19
  GROUP BY user
  ORDER BY times DESC
  </code></pre>
  
  <p>The <code>EXPLAIN</code> output:</p>
  
  <pre><code>| id | select_type | table   | type | possible_keys | key   | key_len | 
  |  1 |  SIMPLE     | matches |  ref | game,i_gfu    | i_gfu |    5    | 
  
  |  ref        |   rows |   Extra                                      |
  | const,const | 155855 | Using where; Using temporary; Using filesort |
  </code></pre>
  
  <p>Is there any way I can make it faster? The table has about 800K records.</p>
  
  <hr>
  
  <p>EDIT: I changed <code>COUNT(id)</code> into <code>COUNT(*)</code> and the time dropped to 0.08 - 0.12 seconds. I think I've tried that before making the index and forgot to change it again after.</p>
  
  <p>In the explain output the <strong>Using index</strong> explains the speeding up:</p>
  
  <pre><code>|   rows |   Extra                                                   |
  | 168029 | Using where; Using index; Using temporary; Using filesort |
  </code></pre>
  
  <p>(Side question: is this dropping of a factor of 5 normal?)</p>
  
  <p>There are about 2000 users, so the final sorting, even if it uses filesort, it doesn't hurt performance. I tried without <code>ORDER BY</code> and it still takes almost same time.</p>