How MySql optimises LIMIT 1 with ORDER BY by expression

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?

Here is an example of the SQL query:

SELECT item.item_id, log(n_votes)score((p0*-0.159223)+(p1*-0.424596)+(p2*-0.424596)+(p30.530745)+(p40.212298)+(p5*-0.106149)+(p60.424596)+(p70.212298)+(p8*-0.159223)+(p9*0.159223)) AS sim FROM item JOIN location USING(location_id) ORDER BY sim DESC LIMIT 1;

The values change per query, so its obvious that mysql will need to look at each row. Thats ok as long as its done efficiently. Is there anyway to calculate this with a index scan NOT a table scan? I tried creating an index across the fields p0-p9 but that did not help. If mysql was intelligent enough it could work out this query using an index scan (probably in memory)…

Yes it does filesort.

Run explain and you’ll see “filesort” which means it will be slow.

Generally sorting by expression is very bad idea - it is best to have it precomputed and stored in column. Which is though does not work for all applications.