MySQL Forums
Forum List  »  Partitioning

Re: Partition Pruning based on subquery / join result
Posted by: Justin Holdsworth
Date: July 12, 2010 05:05AM

Table Creation Statements

Table: dim_dates
Create Table: CREATE TABLE `dim_dates` (
  `date_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `date_text` varchar(60) NOT NULL,
  `date_type` char(1) NOT NULL,
  `period_start` date DEFAULT NULL,
  `period_end` date DEFAULT NULL,
  PRIMARY KEY (`date_id`),
  UNIQUE KEY `idx_date` (`date_text`),
  KEY `idx_date_start` (`period_start`) USING BTREE,
  KEY `idx_date_end` (`period_end`)
) ENGINE=MyISAM AUTO_INCREMENT=140 DEFAULT CHARSET=latin1

TIA,
Justin


Table: fact_final3
Create Table: CREATE TABLE `fact_final3` (
`Cbal` varchar(10) DEFAULT NULL,
`Local_Government_Use_only` varchar(10) DEFAULT NULL,
`Non_ID_Exceptions` varchar(30) DEFAULT NULL,
`Obal` varchar(10) DEFAULT NULL,
`amount` int(11) DEFAULT NULL,
`department_id` smallint(5) unsigned DEFAULT NULL,
`department_id_1` smallint(5) unsigned DEFAULT NULL,
`data_type_id` smallint(5) unsigned DEFAULT NULL,
`programme_object_id` smallint(5) unsigned DEFAULT NULL,
`account_id` smallint(5) unsigned DEFAULT NULL,
`nac_id` smallint(5) unsigned DEFAULT NULL,
`authority_id` smallint(5) unsigned DEFAULT NULL,
`boundary_id` smallint(5) unsigned DEFAULT NULL,
`budget_capital_id` smallint(5) unsigned DEFAULT NULL,
`resource_capital_id` smallint(5) unsigned DEFAULT NULL,
`activitycode_id` smallint(5) unsigned DEFAULT NULL,
`programmeadmin_id` smallint(5) unsigned DEFAULT NULL,
`accountcapital_id` smallint(5) unsigned DEFAULT NULL,
`cofog_id` smallint(5) unsigned DEFAULT NULL,
`cgatype_id` smallint(5) unsigned DEFAULT NULL,
`territory_id` smallint(5) unsigned DEFAULT NULL,
`esa_id` smallint(5) unsigned DEFAULT NULL,
`sector_id` smallint(5) unsigned DEFAULT NULL,
`date_id` smallint(5) unsigned DEFAULT NULL,
KEY `idx_department` (`department_id`),
KEY `idx_datatype` (`data_type_id`),
KEY `idx_programmeobject` (`programme_object_id`),
KEY `idx_account` (`account_id`),
KEY `idx_nac` (`nac_id`),
KEY `idx_authority` (`authority_id`),
KEY `idx_cofog` (`cofog_id`),
KEY `idx_territory` (`territory_id`),
KEY `ida_esa` (`esa_id`),
KEY `idx_sector` (`sector_id`),
KEY `idx_date` (`date_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (date_id)
(PARTITION p0 VALUES LESS THAN (12) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (24) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (36) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (48) ENGINE = MyISAM,
PARTITION p4 VALUES LESS THAN (60) ENGINE = MyISAM,
PARTITION p5 VALUES LESS THAN (72) ENGINE = MyISAM,
PARTITION p6 VALUES LESS THAN (84) ENGINE = MyISAM,
PARTITION p7 VALUES LESS THAN (96) ENGINE = MyISAM,
PARTITION p8 VALUES LESS THAN (108) ENGINE = MyISAM,
PARTITION p9 VALUES LESS THAN (120) ENGINE = MyISAM,
PARTITION p10 VALUES LESS THAN (132) ENGINE = MyISAM,
PARTITION p11 VALUES LESS THAN (144) ENGINE = MyISAM,
PARTITION p12 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
[/code]

Table Information
Table Info
           Name: dim_dates
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 139
 Avg_row_length: 30
    Data_length: 4200
Max_data_length: 281474976710655
   Index_length: 14336
      Data_free: 0
 Auto_increment: 140
    Create_time: 2010-07-09 08:35:04
    Update_time: 2010-07-09 08:35:04
     Check_time: 2010-07-09 08:35:04
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:

           Name: fact_final3
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 10233181
 Avg_row_length: 56
    Data_length: 574908772
Max_data_length: 0
   Index_length: 1035704320
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2010-07-09 08:31:09
    Update_time: 2010-07-09 08:33:57
     Check_time: 2010-07-09 08:52:02
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: partitioned
        Comment:

Explain plan for Query 1
select ff.department_id, year(dd.period_start), sum(ff.amount)
from fact_final3 as ff
join dim_dates as dd on ff.date_id = dd.date_id
where dd.date_text like 'July %'
group by 1,2 order by 1,2 desc

           id: 1
  select_type: SIMPLE
        table: dd
   partitions: NULL
         type: range
possible_keys: PRIMARY,idx_date
          key: idx_date
      key_len: 62
          ref: NULL
         rows: 10
        Extra: Using where; Using temporary; Using filesort

           id: 1
  select_type: SIMPLE
        table: ff
   partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12
         type: ref
possible_keys: idx_date
          key: idx_date
      key_len: 3
          ref: coins.dd.date_id
         rows: 605174
        Extra: Using where

Explain plan for query 2

select ff.department_id, year(dd.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 \G

           id: 1
  select_type: SIMPLE
        table: ff
   partitions: p1,p2,p3,p4,p5,p6,p7,p8
         type: range
possible_keys: idx_date
          key: idx_date
      key_len: 3
          ref: NULL
         rows: 461293
        Extra: Using where; Using temporary; Using filesort

           id: 1
  select_type: SIMPLE
        table: dd
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: coins.ff.date_id
         rows: 1
        Extra:

Buffer Variables (index buffer only 64M on test machine but only abou 50% used)
Buffer Variables
Variable_name   Value
bulk_insert_buffer_size 8388608
join_buffer_size    2097152
key_buffer_size 67108864
myisam_sort_buffer_size 422576128
net_buffer_length   16384
preload_buffer_size 32768
read_buffer_size    8388608
read_rnd_buffer_size    8388608
sort_buffer_size    8388608
sql_buffer_result   OFF

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Partition Pruning based on subquery / join result
2327
July 12, 2010 05:05AM


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.