Jerry Stuckle wrote:
> ashore wrote:
>> Folks, I want to SELECT the single highest value of x. What does
>> "Order by" really order, the full set of table rows before the single
>> selection is returned, or only the returned row? (which wd clearly be
>> useless in this case.)
>>
>> Or is there better SQL? Thanks.
>>
>> AS
>>
>
> SELECT MAX(x) FROM mytable;
>
> You can also add LIMIT 1 if you have 2 or more rows with the same max
> value.
>
> ORDER BY must sort before LIMIT so the server knows which rows should be
> affected by LIMIT.

The query engine is smarter than that. If the ORDER BY clause
is based on a key, then SELECT ... ORDER BY ... LIMIT 1 can
be optimized and is a very cheap operation. Otherwise, it
takes a pass through the whole database. See
"http://dev.mysql.com/doc/refman/5.0/en/limit-optimization.html"
LIMIT N is handled differently for small values of N.

SELECT MAX() is optimized, too, but due to a bug, optimization
may not occur in MySQL before 5.0.18. See "http://bugs.mysql.com/bug.php?id=16016".

Of course, if the field isn't indexed, a pass through the whole
database is unavoidable.

John Nagle