title: MySQL GROUP BY optimization
body: <p>This question is a more specific version of <a href="https://stackoverflow.com/questions/4265544/subqueries-and-mysql-cache-for-18m-row-table">a previous question I asked</a></p>
  
  <h2>Table</h2>
  
  <hr>
  
  <pre><code>CREATE TABLE Test4_ClusterMatches 
  (
      `match_index` INT UNSIGNED,
      `cluster_index` INT UNSIGNED, 
      `id` INT NOT NULL AUTO_INCREMENT,
      `tfidf` FLOAT,
      PRIMARY KEY (`cluster_index`,`match_index`,`id`)
  );</code></pre>
  
  <h2>The query I want to run</h2>
  
  <hr>
  
  <pre><code>mysql> explain SELECT `match_index`, SUM(`tfidf`) AS total 
  FROM Test4_ClusterMatches WHERE `cluster_index` IN (1,2,3 ... 3000) 
  GROUP BY `match_index`;</code></pre>
  
  <h2>The Problem with the query</h2>
  
  <hr>
  
  <p>It uses temporary and filesort so its to slow<pre><code>+----+-------------+----------------------+-------+---------------+---------+---------+------+-------+-----------------------------------------------------------+
  | id | select_type | table                | type  | possible_keys | key     | key_len | ref  | rows  | Extra                                                     |
  +----+-------------+----------------------+-------+---------------+---------+---------+------+-------+-----------------------------------------------------------+
  |  1 | SIMPLE      | Test4_ClusterMatches | range | PRIMARY       | PRIMARY | 4       | NULL | 51540 | Using where; Using index; Using temporary; Using filesort | 
  +----+-------------+----------------------+-------+---------------+---------+---------+------+-------+-----------------------------------------------------------+</code></pre>
  With the current indexing the query would need to sort by cluster_index first to eliminate the use of temporary and filesort, but doing so gives the wrong results for sum(tfidf). 
  Changing the primary key to</p>
  
  <pre><code>PRIMARY KEY (`match_index`,`cluster_index`,`id`)</code></pre>
  
  <p>Doesn't use file sort or temp tables but it uses 14,932,441 rows so it is also to slow</p>
  
  <pre><code>+----+-------------+----------------------+-------+---------------+---------+---------+------+----------+--------------------------+
  | id | select_type | table                | type  | possible_keys | key     | key_len | ref  | rows     | Extra                    |
  +----+-------------+----------------------+-------+---------------+---------+---------+------+----------+--------------------------+
  |  1 | SIMPLE      | Test5_ClusterMatches | index | NULL          | PRIMARY | 16      | NULL | 14932441 | Using where; Using index | 
  +----+-------------+----------------------+-------+---------------+---------+---------+------+----------+--------------------------+</code></pre>
  
  <h2>Tight Index Scan</h2>
  
  <hr>
  
  <p><a href="http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html#tight-index-scan" rel="nofollow noreferrer">Using tight index scan</a> by running the search for just one index
  <pre><code>mysql> explain SELECT <code>match_index</code>, SUM(<code>tfidf</code>) AS total
  FROM Test4_ClusterMatches WHERE <code>cluster_index</code> =3000 
  GROUP BY <code>match_index</code>;</code></pre>Eliminates the temporary tables and filesort.
  <pre><code>+----+-------------+----------------------+------+---------------+---------+---------+-------+------+--------------------------+
  | id | select_type | table                | type | possible_keys | key     | key_len | ref   | rows | Extra                    |
  +----+-------------+----------------------+------+---------------+---------+---------+-------+------+--------------------------+
  |  1 | SIMPLE      | Test4_ClusterMatches | ref  | PRIMARY       | PRIMARY | 4       | const |   27 | Using where; Using index | 
  +----+-------------+----------------------+------+---------------+---------+---------+-------+------+--------------------------+</code></pre> I'm not sure if this can be exploited with some magic sql-fu that I haven't come across yet?
  <br><br><h1>Question</h1></p>
  
  <hr>
  
  <p>How can I change my query so that it use 3,000 cluster_indexes, avoids using temporary and filesort without it needing to use 14,932,441 rows?</p>
  
  <p><br><br><h1>Update</h1></p>
  
  <hr>
  
  <p>Using the table</p>
  
  <pre><code>CREATE TABLE Test6_ClusterMatches 
  (
    match_index INT UNSIGNED,
    cluster_index INT UNSIGNED, 
    id INT NOT NULL AUTO_INCREMENT,
    tfidf FLOAT,
    PRIMARY KEY (id),
    UNIQUE KEY(cluster_index,match_index)
  );</code></pre>
  
  <p>The query below then gives 10 rows in set (0.41 sec) :)</p>
  
  <pre><code>SELECT `match_index`, SUM(`tfidf`) AS total FROM Test6_ClusterMatches WHERE 
  `cluster_index` IN (.....)
  GROUP BY `match_index` ORDER BY total DESC LIMIT 0,10;</code></pre>
  
  <p>but its using temporary and filesort</p>
  
  <pre><code>+----+-------------+----------------------+-------+---------------+---------------+---------+------+-------+----------------------------------------------+
  | id | select_type | table                | type  | possible_keys | key           | key_len | ref  | rows  | Extra                                        |
  +----+-------------+----------------------+-------+---------------+---------------+---------+------+-------+----------------------------------------------+
  |  1 | SIMPLE      | Test6_ClusterMatches | range | cluster_index | cluster_index | 5       | NULL | 78663 | Using where; Using temporary; Using filesort | 
  +----+-------------+----------------------+-------+---------------+---------------+---------+------+-------+----------------------------------------------+
  </code></pre>
  
  <p>I'm wondering if theres anyway to get it faster by eliminating the using temporary and using filesort?</p>