selective partition scanning with joins

[niklas]

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