PHWinfo - Afficher un message - SELECT MAX vs. SELECT / ORDER BYDESC / LIMIT

04/10/2007, 18h09

yawnmoth wrote:
> I'm curious - which query is generally faster?:
>
> SELECT MAX(order_total) AS order_max
> FROM orders
> WHERE customer = ...
>
> ...or...
>
> SELECT order_total AS order_max
> FROM orders
> WHERE customer = ...
> ORDER BY order_total DESC
> LIMIT 1

The latter (especially where there is an index on order_total).

There is no good reason I can see why the former could not be made as good,
but all the tests I have done have shown that the latter wins hands down.

Incidentally, I was curious of this a whle ago, which was when I tested it.
You could have done the same. You learn a lot by trying this sort of
exercise.

04/10/2007, 18h09	#2
Paul Lautman Messages: n/a Hébergeur:	Re: SELECT MAX vs. SELECT / ORDER BYDESC / LIMIT yawnmoth wrote: > I'm curious - which query is generally faster?: > > SELECT MAX(order_total) AS order_max > FROM orders > WHERE customer = ... > > ...or... > > SELECT order_total AS order_max > FROM orders > WHERE customer = ... > ORDER BY order_total DESC > LIMIT 1 The latter (especially where there is an index on order_total). There is no good reason I can see why the former could not be made as good, but all the tests I have done have shown that the latter wins hands down. Incidentally, I was curious of this a whle ago, which was when I tested it. You could have done the same. You learn a lot by trying this sort of exercise.