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

Your example and ones like :

SELECT max(foo), count(foo) FROM bar
SELECT max(a.foo1), max(b.foo2) FROM bar1 AS a NATURAL JOIN bar2 AS b

have made me realize that the scope of "what should be optimized" is 
somewhat subtle.

I am inclined to keep it simple (i.e rather limited) for a first cut, 
and if that works well, then look at extending to more complex rewrites.

What do you think?


Jim C. Nasby wrote:

>On Thu, Nov 11, 2004 at 11:48:49AM +1300, Mark Kirkwood wrote:
>  
>
>>I am looking at implementing this TODO item. e.g. (max case):
>>
>>rewrite
>>SELECT max(foo) FROM bar
>>as
>>SELECT foo FROM bar ORDER BY foo DESC LIMIT 1
>>if there is an index on bar(foo)
>>    
>>
> 
>Out of curiosity, will you be doing this in such a way that 
>
>SELECT min(foo), max(foo) FROM bar
>
>will end up as
>
>SELECT (SELECT foo FROM bar ORDER BY foo ASC LIMIT 1), (SELECT ... DESC
>LIMIT 1)
>
>?
>  
>