title: Why does this MySQL query take so long for sorting even though it has an index?
body: <p>I don't understand why the following query takes so long to sort. In the profiling it shows that the sorting takes very long even though there is an index for the sorting row.</p>
  
  <p>The MySQL server is a dedicated machine, basically running nothing but MySQL in the current Debian stable version with 32 GB physical RAM and a decent MySQL configuration. Table <code>player</code> (INNODB) currently has ~32,000,000 entries. Indexes for the table <code>player</code>:</p>
  
  <p><code>gameplayer</code> over <code>game, id, server</code> (unique)</p>
  
  <p><code>serverplayer</code> over <code>id, server</code> (index)</p>
  
  <p><code>date</code> over <code>date</code> (index) &lt;-- this is what we will sort by</p>
  
  <p>The following query is used to get data about a certain player's stats for a game, thus getting information about the player, the stats and the actual game:</p>
  
  <pre><code>set profiling = 1;
  
  SELECT *
  FROM `player` `gs`
  INNER JOIN `playerstats` `gss` ON (`gss`.`player` = `gs`.`id`)
  INNER JOIN `game` `g` ON (`g`.`id` = `gs`.`game`)
  WHERE `gs`.`id` = :id
  AND `gs`.`server` = :server
  ORDER BY `gs`.`date` DESC
  LIMIT 10;
  
  show profile for query 1;
  
  set profiling = 0;
  </code></pre>
  
  <p>Here is the result for show profile:</p>
  
  <pre><code>starting    0.000124
  Waiting for query cache lock    0.000070
  checking query cache for query  0.000090
  checking permissions    0.000066
  checking permissions    0.000058
  checking permissions    0.000056
  Opening tables  0.000074
  System lock 0.000058
  Waiting for query cache lock    0.000093
  init    0.000071
  optimizing  0.000054
  statistics  0.000136
  preparing   0.000073
  executing   0.000044
  Sorting result  0.201156 // &lt;========================
  Sending data    0.042253
  end 0.000046
  query end   0.000038
  closing tables  0.000043
  freeing items   0.000042
  Waiting for query cache lock    0.000037
  freeing items   0.000039
  Waiting for query cache lock    0.000035
  freeing items   0.000032
  storing result in query cache   0.000031
  logging slow query  0.000030
  cleaning up 0.000031
  </code></pre>
  
  <p>If I understand MySQL right, then it should use the <code>serverplayer</code> index for the WHERE conditions and the <code>date</code> index for the sorting, right? Then why would it take so long, when I have proper indexes available?</p>
  
  <p>Or do I have to add the column <code>date</code> to the <code>serverplayer</code> index?</p>