MySQL Forums
Forum List  »  Partitioning

Partition Pruning based on subquery / join result
Posted by: Justin Holdsworth
Date: July 09, 2010 03:54AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Partition Pruning based on subquery / join result
5329
July 09, 2010 03:54AM


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.