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

06/10/2007, 23h32

Séverin Richard wrote:
> 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
> according to me
>> SELECT MAX(order_total) AS order_max
>> FROM orders
>> WHERE customer = ...
> is faster
>
> with a limitation that is:
>> SELECT MAX(order_total) AS order_max,****ID_ORDER****
>> FROM orders
>> WHERE customer = ...
> will give u a random ****ID_ORDER****
> (not the one with the max order)
>
> and
>> SELECT order_total AS order_max, ****ID_ORDER****
>> FROM orders
>> WHERE customer = ...
>> ORDER BY order_total DESC
>> LIMIT 1
> will give u the right ****ID_ORDER****
>
> That is the amazing way that the MAX() function works(fast).
>
>
Please do not top post. (Top posting fixed)

I have tested it and found that the speed advantage is the opposite way
around from what you suggest. Have you tried it?

06/10/2007, 23h32	#6
Paul Lautman Messages: n/a Hébergeur:	Re: SELECT MAX vs. SELECT / ORDER BYDESC / LIMIT Séverin Richard wrote: > 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 > according to me >> SELECT MAX(order_total) AS order_max >> FROM orders >> WHERE customer = ... > is faster > > with a limitation that is: >> SELECT MAX(order_total) AS order_max,**ID_ORDER >> FROM orders >> WHERE customer = ... > will give u a random ID_ORDER > (not the one with the max order) > > and >> SELECT order_total AS order_max, ID_ORDER >> FROM orders >> WHERE customer = ... >> ORDER BY order_total DESC >> LIMIT 1 > will give u the right ID_ORDER** > > That is the amazing way that the MAX() function works(fast). > > Please do not top post. (Top posting fixed) I have tested it and found that the speed advantage is the opposite way around from what you suggest. Have you tried it?