List:General Discussion« Previous MessageNext Message »
From:Erik Liljencrantz Date:April 29 1999 12:39pm
Subject:Re: Select most recent row - another one for the FAQ perhaps?
View as plain text  
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.

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.

Regards,
  Erik L.

---
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
Thread
Select most recent row - another one for the FAQ perhaps?Fred Read29 Apr
  • Re: Select most recent row - another one for the FAQ perhaps?Robin Bowes29 Apr
  • Re: Select most recent row - another one for the FAQ perhaps?Fred Read29 Apr
  • Re: Select most recent row - another one for the FAQ perhaps?Robin Bowes29 Apr
  • Re: Select most recent row - another one for the FAQ perhaps?Christian Mack29 Apr
    • Re: Select most recent row - another one for the FAQ perhaps?Fraser MacKenzie29 Apr
      • Re: Select most recent row - another one for the FAQ perhaps?Fred Read29 Apr
        • Select most recent row - another one for the FAQ perhaps?Fraser MacKenzie29 Apr
    • Re: Select most recent row - another one for the FAQ perhaps?Erik Liljencrantz29 Apr
  • Re: Select most recent row - another one for the FAQ perhaps?Fred Read29 Apr
    • Re: Select most recent row - another one for the FAQ perhaps?Michael Longval1 May
      • Re: Select most recent row - another one for the FAQ perhaps?Thimble Smith1 May
  • Re: Select most recent row - another one for the FAQ perhaps?Robin Bowes29 Apr
    • Re: Select most recent row - another one for the FAQ perhaps?Erik Liljencrantz29 Apr
      • Re: Select most recent row - another one for the FAQ perhaps?Michael Widenius30 Apr