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

On 11/10/2004 11:57 PM, Mark Kirkwood wrote:

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

The problem is, that

select min(foo) from bar where foo > 100;

is still solvable with an index scan, assuming there is an index on foo.

But

select min(foo) from bar where baz = 'IT';

is only doable with an index scan if you have a compound index on (foo,baz).

Both cases can be expressed with order by + limit queries, that would
indeed utilize those indexes. But what's been discussed so far does not
cover any of them.

Jan

>
>
> 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)
>>
>>?
>>
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #