How to optimise a query to avoid filesort on LARGE resultset

[Marek Narkiewicz]

Hi there. I have the following query:

SELECT whenadded
FROM LogAllEvents
WHERE kioskid=x
ORDER BY whenadded DESC
LIMIT 1

And the following relevant indexes:

UNIQUE KEY `localid` (`localid`,`kioskid`,`whenoccured`,`category`),
KEY `whenadded` (`whenadded`),
KEY `kioskid` (`kioskid`)

By my understanding that should avoid filesort but the problem (I think) is that the table is 19 million records or more, and the result set can be 20 thousand or more.

EXPLAIN gives me:
possible_keys: kioskid
key: kioskid
Extra: Using where; Using filesort

Is there any way to handle this sort in memory or is my query set up wrong? I have 10mb in my sort_buffer and 800mb in my key buffer.

Many thanks.
Maz