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

05/10/2007, 10h26

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?

05/10/2007, 10h26	#4
Captain Paralytic Messages: n/a Hébergeur:	Re: SELECT MAX vs. SELECT / ORDER BYDESC / LIMIT 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?