Re: Select most recent row - another one for the FAQ perhaps?

[Robin Bowes]

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: [phone]  Fax: [phone]