MySQL Forums
Forum List  »  Partitioning

selective partition scanning with joins
Posted by: niklas
Date: September 21, 2006 04:27AM

Hi,

I have a setup where I have two tables, one with measurements (10M rows) and one lookup tables with the identities of what is being measured.

The measurement table is then partition using RANGE on the numerical id.

The two tables are typically joined by an id (BIGINT) in a query where i join the two tables together using <id> and filter using a name/description from the lookup table.

The problem is that even if I run a select which only returns a single entry from the lookup table, all partitions in the measurement table is scanned.

It can be tested like this:

drop table if exists t1;
CREATE TABLE `t1` (
`a` int(11) NOT NULL,
`dt` datetime NOT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`a`,`dt`)
) ENGINE=InnoDB;

drop table if exists lookup;
CREATE TABLE `lookup` (
`a` int(11) NOT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB;

alter table t1
partition by range (a) (
PARTITION p1 VALUES LESS THAN ( 1 ),
PARTITION p2 VALUES LESS THAN ( 2 ),
PARTITION p3 VALUES LESS THAN ( 3 ),
PARTITION p4 VALUES LESS THAN ( 4 ),
PARTITION p_MAXIMUM VALUES LESS THAN MAXVALUE );

insert into t1 values (1,now(),1,1),
(2,now(),1,1),
(3,now(),1,1),
(4,now(),1,1);
insert into lookup values (1,1),
(2,3);


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?

Thanks,

Niklas

Options: ReplyQuote


Subject
Views
Written By
Posted
selective partition scanning with joins
5583
September 21, 2006 04:27AM
2713
December 09, 2006 08:25AM
4055
December 10, 2006 11:52PM
3995
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.