>>>>> "Erik" == Erik Liljencrantz <erik@stripped> writes:
Erik> At 13:08 1999-04-29 +0100, Robin Bowes wrote:
>> [...]
>>> order by Created desc limit 1
>>
>> This solution did occur to me and it will most certainly work but I have
>> always put off using "limit" queries by the possible performance
>> impact.
Erik> The performance "hog" is not "limit" but "order by". Sorting can be
Erik> expensive whereas limit only limits the number of rows returned (and mySQL
Erik> won't get more rows than necessary internally either). The combination
Erik> order and limit should make mySQL discard rows beyond the limit and fewer
Erik> rows have to be kept in the sorted list.
>> Just how good is the optimisation? Surely if the result set is
>> large and involves joins then this operation may take quite a while?
Erik> I've used the "order by xxx limit" combo for fairly large tables and with
Erik> "limit 1" I believe the performance is about the same as with max() or
Erik> min() for the same field. Without looking at the code I still guess that
Erik> the optimizer reduce both statements to almost identical operations internally.
Yes, it does!
If you you 'desc' on a key with 'limit', MySQL will search after the
last row in the table and to 'read-previous-key' until the limit is
satisfied.
Erik> For joins I remember a strange performance problem involving a combination
Erik> of a join and order by, a query that ran much faster by doing several
Erik> selects instead. But this was with the very old version 3.20 (my fault for
Erik> not upgrading earlier).
The above optimization only works if you are doing an 'order by' on
a key in the first table in the join.
<cut>
Regards,
Monty
