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

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.

> > 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.

Hmm, funny. In another database system I use, I could easily say: no
difference.
Cause the index would most probably be used on CUSTOMER (assuming
there would be one, on this foreign key column) and indeed, optionally on
ORDER_TOTAL. The optimizer could use the latter index (if CUSTOMER
isn't unique, of course) for both ORDER BY DESC and MAX :-)

So, this is different in MySQL?

And, of course, the question to the original poster: is Customer in this
case
unique? If so, I'd still be saying: no difference.


--
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

On 5 Oct, 08:23, "Martijn Tonies" <m.ton...@upscene.removethis.com>
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.
>
> Hmm, funny. In another database system I use, I could easily say: no
> difference.
> Cause the index would most probably be used on CUSTOMER (assuming
> there would be one, on this foreign key column) and indeed, optionally on
> ORDER_TOTAL. The optimizer could use the latter index (if CUSTOMER
> isn't unique, of course) for both ORDER BY DESC and MAX :-)
>
> So, this is different in MySQL?
>
> And, of course, the question to the original poster: is Customer in this
> case
> unique? If so, I'd still be saying: no difference.
>
> --
> Martijn Tonies
> Database Workbench - development tool for MySQL, and more!
> Upscene Productionshttp://www.upscene.com
> My thoughts:http://blog.upscene.com/martijn/
> Database development questions? Check the forum!http://www.databasedevelopmentforum.com- Hide quoted text -
>
> - Show quoted text -

As I commented, I would have expected to be able to say "no
difference", but my tests prove otherwise.

Another thing I am curious about and am about to test, is whether an
index can make a difference when doing a SELECT DISTINCT on a column
in a large table. If I was programming thebasics in VSAM, I know how I
could use such an index to optimise this task, but will MySQL do
similar?

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).


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
>

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?