|
Consider this example
create table ten(a int);
|
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
create table t1 (a int, b int, c int);
|
insert into t1 select a,a,a from ten;
|
create table t2 (a int, b int, c int);
|
insert into t2 select a,a,a from ten;
|
|
create table tp (
|
part_id int,
|
a int
|
) partition by list (part_id) (
|
partition p0 values in (0),
|
partition p1 values in (1),
|
partition p2 values in (2),
|
partition p3 values in (3),
|
partition p4 values in (4)
|
);
|
insert into tp select mod(a,5), a from ten;
|
Let's put the partitioned table on the inner side of the outer join, and let the ON expression have a condition that's usable by partition pruning:
explain partitions select * from t1 left join tp on (t1.a=tp.a and tp.part_id=3);
|
+------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+
|
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 10 | |
|
| 1 | SIMPLE | tp | p3 | ALL | NULL | NULL | NULL | NULL | 2 | Using where; Using join buffer (flat, BNL join) |
|
+------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+
|
tp.part_id=3 gets us tp.partitions=p3. Good so far.
Now, let's change the outer join to have two tables:
mysql> explain partitions
|
-> select * from t1 left join (tp join t2) on (t1.a=tp.a and tp.a=t2.b and tp.part_id=3);
|
+------+-------------+-------+----------------+------+---------------+------+---------+------+------+--------------------------------------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+----------------+------+---------------+------+---------+------+------+--------------------------------------------------------+
|
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 10 | |
|
| 1 | SIMPLE | tp | p0,p1,p2,p3,p4 | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using join buffer (incremental, BNL join) |
|
+------+-------------+-------+----------------+------+---------------+------+---------+------+------+--------------------------------------------------------+
|
now, tp.partitions includes all partitions even if the ON expression has a condition that restricts it to one.
|