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