MySQL Forums
Forum List  »  Partitioning

Re: selective partition scanning with joins
Posted by: Mikael Ronström
Date: September 21, 2006 03:27PM

Hi,

niklas Wrote:
-------------------------------------------------------

> The following statment will scan all partitions:
> explain partitions
> select * from t1 left join lookup on
> (t1.a=lookup.b)
> where
> lookup.a=2
>
> whereas the following does not:
>
> explain partitions
> select * from t1 left join lookup on
> (t1.a=lookup.b)
> where
> lookup.a=2
> and lookup.b=3
>
>
> How is this intended to work in the case of joins?
> Is there a way of optmizing this some way?
>

The reason that the second query gets optimised better is because
the join condition can be substituted with t1.a = 3. Not sure
exactly how the optimiser converts this internally but I know that
the first expression has no bounds on the a field in the join
condition.

Rgrds Mikael

> Thanks,
>
> Niklas

Options: ReplyQuote


Subject
Views
Written By
Posted
5437
September 21, 2006 04:27AM
Re: selective partition scanning with joins
3330
September 21, 2006 03:27PM
2648
December 09, 2006 08:25AM
3949
December 10, 2006 11:52PM
3918
December 15, 2006 07:45AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.