How can LIMIT 1 possibly make a query slower

Question

Given this table in MySQL 5.6:

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)
)

How can it possibly be that this query returns about 2000 rows instantly:

SELECT * FROM PlayerSession
WHERE player_id = ....
ORDER BY lastActivity DESC

but adding LIMIT 1 makes it take 4 seconds, even though all that should do is pick the first result?

Using EXPLAIN I found the only difference to be that without the limit, filesort is used. From what I gather, this should make it slower, not faster. The whole table contains about 2M rows. Also, adding LIMIT 3 or anything higher than that, gives the same performance as no limit.

And yes, I have since created an index on playerId, lastActivity, 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.

Answer 1

What specific version of 5.6? Please provide EXPLAIN FORMAT=JSON SELECT .... Please provide SHOW CREATE TABLE; we need to see the other indexes, plus datatypes.

INDEX(playerId, lastActivity) lets the query avoid "filesort".

A possible reason for the strange timings could be caching. Run each query twice to avoid that hiccup.