MySQL Performance: MySQL => How MySql optimises LIMIT 1 with ORDER BY by expression

How MySql optimises LIMIT 1 with ORDER BY by expression [message #1721]

Mon, 03 September 2007 07:31

al.james
Messages: 16
Registered: October 2006

Junior Member

Hi there...

If I have a query that returns a single row (i.e. LIMIT 1) of a table ordered by an expression (I know order by expressions is sub-optimal, but its unavoidable).

I realise that a table scan is unavoidable, but what bothers me is that running EXPLAIN on my query says that mysql is using filesort.

Does this mean that it is sorting the entire table by this expression and then returns the first? This is crazy, clearly the lowest (or highest) scoring row could be pulled out during the table scan without an extra sorting. Indeed, this could apply to any LIMIT X query where X is quite small. You would just need to maintain a sorted list of those X...

Can someone confirm that mysql is indeed doing a full sort? Is there anyway to find out?

Report message to a moderator