Re: MAX/MIN optimization via rewrite (plus query rewrites generally)

On Thu, Nov 11, 2004 at 08:00:01AM -0600, Bruno Wolff III wrote:
> On Thu, Nov 11, 2004 at 17:52:19 +1100,
> John Hansen <john(at)geeknet(dot)com(dot)au> wrote:
> > Why not just change the function all together to 'select $1 from $2
> > order by $1 desc limit 1;'
> >
> > Is there ANY situation where max(col) as it is, would be faster?
>
> Yes. A couple I can think of are:
> When count(col) is also being used.

Technically, wouldn't that depend on how many rows you were processing?
Certainly the time required for the CPU to compare the value of a field
in the current row to what it's got stored as the current maximum is
small compared to a disk read, but at some point it will be faster to
read an index.

> When a GROUP BY is being used and there isn't an index that can both be used
> to do the grouping and col order within each group.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"