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
The performance "hog" is not "limit" but "order by". Sorting can be
expensive whereas limit only limits the number of rows returned (and mySQL
won't get more rows than necessary internally either). The combination
order and limit should make mySQL discard rows beyond the limit and fewer
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?
I've used the "order by xxx limit" combo for fairly large tables and with
"limit 1" I believe the performance is about the same as with max() or
min() for the same field. Without looking at the code I still guess that
the optimizer reduce both statements to almost identical operations internally.
For joins I remember a strange performance problem involving a combination
of a join and order by, a query that ran much faster by doing several
selects instead. But this was with the very old version 3.20 (my fault for
not upgrading earlier).
>I'm not saying that "limit" is a bad thing, rather that it is a concern
>of mine. Anyone done ay performance tests?
Tried these queries on a table with about 100 fields and 105 000 records
where counter is an int field without any index:
select max(counter) from foretag
select counter from foretag order by counter desc limit 1
Both returned in about 10-13 seconds (tested several times). Variation
depending on load, not the query.
With index on counter they both run in no time.
Erik Liljencrantz, E-ell-Data, http://www.eldata.se
erik@stripped, +46 150 250 58, +46 70 51 8 51 26
Aspenaes, 640 25 JULITA, SWEDEN