List:General Discussion« Previous MessageNext Message »
From:Robin Bowes Date:April 29 1999 12:08pm
Subject:Re: Select most recent row - another one for the FAQ perhaps?
View as plain text  
Erik Liljencrantz wrote:

> Maybe I'm missing something, but "order by fieldname desc limit 1" usually
> gets me the record where the field has the largest value (or smallest
> without desc).
> 
> In this case:
>   select UserName, Created
>     from MyTable
>     where UserName = 'MyUserName'
>     order by Created desc limit 1
> 
> To simple to work? mySQL knows how to optimize this type of query.

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.  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'm not saying that "limit" is a bad thing, rather that it is a concern
of mine.  Anyone done ay performance tests?

R.
-- 
Robin Bowes - System Development Manager - Room 405A
E.O.C., Overseas House, Quay St., Manchester, M3 3HN, UK.
Tel: +44 161 838 8321  Fax: +44 161 835 1657
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