title: How can I speed up a slow SUM + ORDER BY + LIMIT query in MySQL?
body: <p>I have a simple key-value table with two fields, created like so:</p>
  
  <pre><code> CREATE TABLE `mytable` (
      `key` varchar(255) NOT NULL,
      `value` double NOT NULL,
      KEY `MYKEY` (`key`)
   );
  </code></pre>
  
  <p>The keys are not unique. The table contains over one million records. I need a query that will sum up all the values for a given key, and return the top 10 keys. Here's my attempt:</p>
  
  <pre><code>  SELECT t.key, SUM(t.value) value
    FROM mytable t
    GROUP BY t.key
    ORDER BY value DESC
    LIMIT 0, 10;
  </code></pre>
  
  <p>But this is <em>very</em> slow. Thing is, without the GROUP BY and SUM, it's pretty fast, and without the ORDER BY, it's very fast, but for some reason the combination of the two makes it very very slow. Can anyone explain why this is so, and how this can be speeded up?</p>
  
  <hr>
  
  <p>There is no index on value. I tried creating one but it didn't help.</p>
  
  <hr>
  
  <p>EXPLAIN EXTENDED produces the following in Workbench:</p>
  
  <pre><code> id select_type table type  possible_keys key   key_len ref  rows    filtered Extra 
   1  SIMPLE      t     index NULL          MYKEY 257     NULL 1340532 100.00   "Using temporary; Using filesort" 
  </code></pre>
  
  <hr>
  
  <p>There are about 400K unique keys in the table.</p>
  
  <hr>
  
  <p>The query takes over 3 minutes to run. I don't know how long because I stopped it after 3 minutes. <strong>However</strong>, if I remove the index on key, it runs in 30 seconds! Anyone has any idea why?</p>