Partition Pruning based on subquery / join result
Morning all,
It's a question about in/subquery and joins with a partition pruning.
MySQL Version : 5.1.46 (win64)
I have a fact table with about 11M rows and a number of smaller dimension tables. The fact table is partitioned using a synthetic key for the actual date into 13 partitions (not quite a month by month split but fairly close).
Our most common type of query is against the fact table using month as part of the restriction.
The issue we are having is that if I explicitly build the query using constant values for the synthetic date key, the optimizer happily prunes out partitions. If the query is built with the synthetic date key being returned from either an IN statement or a join, then no pruning occurs.
For info the explain plans for some example queries are:
Query 1 (using join and a range search) - time for execution ~3.6 secs
select department_id, year(period_start), sum(amount)
from fact_final3 as ff
join dim_dates as dd on ff.date_id = dd.date_id
where date_text like 'July %'
group by 1,2 order by 1,2 desc;
Explain plan for Q1:
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE dd range PRIMARY,idx_date idx_date 62 10
1 SIMPLE ff p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12 ref idx_date idx_date 3 coins.dd.date_id 605174
Query 2 (using constant values equivalent to the date_ids that the join returns) - time for execution: ~ 2.5 secs
select department_id, year(period_start), sum(amount)
from fact_final3 as ff
join dim_dates as dd on ff.date_id = dd.date_id
where dd.date_id in (14,26,38,50,62,74,86,98)
group by 1,2 order by 1,2 desc;
Explain Plan for Q2:
id select_type table partitions type possible_keys key key_len ref rows
1 SIMPLE ff p1,p2,p3,p4,p5,p6,p7,p8 range idx_date idx_date 3 461293
1 SIMPLE dd eq_ref PRIMARY PRIMARY 2 coins.ff.date_id 1
I appreciate that Q2 isn't quite the same, but my question is : why isn't q1 generating partition pruning as the results from the join only span 8 of the 13 partitions ?
TIA,
Justin