title: How can LIMIT 1 possibly make a query slower
body: <p>Given this table in MySQL 5.6:</p>
  
  <pre><code>create table PlayerSession
  (
      id bigint auto_increment primary key,
      lastActivity datetime not null,
      player_id bigint null,
      ...
      constraint FK4410E05525A98981
          foreign key (player_id) references Player (id)
  )
  </code></pre>
  
  <p>How can it possibly be that this query returns about 2000 rows instantly:</p>
  
  <pre><code>SELECT * FROM PlayerSession
  WHERE player_id = ....
  ORDER BY lastActivity DESC
  </code></pre>
  
  <p>but adding <code>LIMIT 1</code> makes it take 4 seconds, even though all that should do is pick the first result?</p>
  
  <p>Using <code>EXPLAIN</code> I found the only difference to be that without the limit, <code>filesort</code> is used. From what I gather, this should make it slower, not faster. The whole table contains about 2M rows.
  Also, adding <code>LIMIT 3</code> or anything higher than that, gives the same performance as no limit.</p>
  
  <p>And yes, I have since created an index on <code>playerId, lastActivity</code>, which, surprise surprise, makes it fast again. While that takes the immediate stress out of the situation (the server was rather overloaded), it doesn't really explain the mystery.</p>