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