MySQL Forums
Forum List  »  Partitioning

Re: partition pruning doesnt work on joined tables?
Posted by: Mikael Ronström
Date: November 28, 2007 03:31PM

Hi,
It should work fine on joined tables as well,
use EXPLAIN PARTITIONS on the query to see
what partitions are pruned. From what I can see
you should only need to use partition d7 on t1 and
t2.

Rgrds Mikael


Wrote:
-------------------------------------------------------
> hello!
>
> does partition pruning work on joined tables?
>
> example tables t1 and t2:
>
> CREATE TABLE t1 (
> lname VARCHAR(50) NOT NULL,
> region_code TINYINT UNSIGNED NOT NULL,
> dob DATE NOT NULL
> )
> PARTITION BY RANGE( YEAR(dob) ) (
> PARTITION d5 VALUES LESS THAN (2000),
> PARTITION d6 VALUES LESS THAN (2005),
> PARTITION d7 VALUES LESS THAN MAXVALUE
> );
>
>
> CREATE TABLE t2 (
> fname VARCHAR(50) NOT NULL,
> region_code TINYINT UNSIGNED NOT NULL,
> dob DATE NOT NULL
> )
> PARTITION BY RANGE( YEAR(dob) ) (
> PARTITION d5 VALUES LESS THAN (2000),
> PARTITION d6 VALUES LESS THAN (2005),
> PARTITION d7 VALUES LESS THAN MAXVALUE
> );
>
> query:
> SELECT fname, lname, COUNT(*) FROM t1, t2 WHERE
> t1.dob >= 2005 AND t1.dob < 2006 AND t2.dob >=
> 2005 AND t2.dob < 2006 AND t1.region_code =
> t2.region_code GROUP BY fname, lname;
>
> i have tested that example and the query runtime
> is the same as without partitioning.
>
> thanks,
> norman

Options: ReplyQuote


Subject
Views
Written By
Posted
3693
November 14, 2007 04:24AM
Re: partition pruning doesnt work on joined tables?
2468
November 28, 2007 03:31PM


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.