Re: Performance problem in InnoDB
Hi,
There is 20GB RAM and I created the index on
FACT table : subnet_key
SubnetDimension : PIDType as per your advise.
The query did execute in 0.58 secs but if I change the PIDType it takes more time ? Is it because of the data or am I doing something wrong here ?
Partitioning is used only for purging the data and I am not using them in the query.
Using the creation_time in the GROUP BY instead of 2 fields (date_key, time_key) didn't given any improvement.
Still I will use the creation_time
Dimension tables:
mysql> show create table B2BUnitDimension\G
*************************** 1. row ***************************
Table: B2BUnitDimension
Create Table: CREATE TABLE `B2BUnitDimension` (
`b2bunit_key` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`component_name` varchar(45) DEFAULT NULL,
`customer_name` varchar(45) DEFAULT NULL,
PRIMARY KEY (`b2bunit_key`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1 COMMENT='Stores the information related to the B2BUnit'
1 row in set (0.00 sec)
mysql> show create table SubnetDimension\G
*************************** 1. row ***************************
Table: SubnetDimension
Create Table: CREATE TABLE `SubnetDimension` (
`subnet_key` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`CIDR` varchar(45) DEFAULT NULL,
`PIDName` varchar(45) DEFAULT NULL,
`PIDType` enum('LOCAL','PEERING','TRANSIT') DEFAULT NULL,
`CustomerName` varchar(45) DEFAULT NULL,
PRIMARY KEY (`subnet_key`),
KEY `idx_SubnetDimension_PIDType` (`PIDType`)
) ENGINE=InnoDB AUTO_INCREMENT=42218 DEFAULT CHARSET=latin1 COMMENT='Stores the information related to the Subnets'
1 row in set (0.00 sec)
select date_key, time_key, sum(return_count)
from FACT_HOUR_SUBNET_TRACKER fact, B2BUnitDimension b2bDim, SubnetDimension subnetDim
where fact.b2bunit_key = b2bDim.b2bunit_key and
fact.subnet_key = subnetDim.subnet_key AND
subnetDim.PIDType = 'LOCAL' and
b2bDim.customer_name = 'turktel'
group by fact.date_key , fact.time_key;
Creating the 2 indexes (Fact table: subnet_key and SubnetDimension: PIDType) improved the performance of the query from 13 secs to 0.58 secs !!!
If I change the PIDType it takes again 10 secs.
mysql> select creation_time, sum(return_count) from FACT_HOUR_SUBNET_TRACKER fact, B2BUnitDimension b2bDim, SubnetDimension subnetDim
where fact.b2bunit_key = b2bDim.b2bunit_key and fact.subnet_key = subnetDim.subnet_key AND subnetDim.PIDType = 'PEERING' and b2bDim.customer_name = 'turktel' group by fact.creation_time;
::
261 rows in set (10.13 sec)
mysql> explain select creation_time, sum(return_count) from FACT_HOUR_SUBNET_TRACKER fact, B2BUnitDimension b2bDim, SubnetDimension subnetDim
where fact.b2bunit_key = b2bDim.b2bunit_key and fact.subnet_key = subnetDim.subnet_key AND subnetDim.PIDType = 'PEERING' and b2bDim.customer_name = 'turktel' group by fact.creation_time\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: subnetDim
type: ref
possible_keys: PRIMARY,idx_SubnetDimension_PIDType
key: idx_SubnetDimension_PIDType
key_len: 2
ref: const
rows: 14678
Extra: Using where; Using index; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: fact
type: ref
possible_keys: idx_Fact_subnet_key
key: idx_Fact_subnet_key
key_len: 4
ref: dwh.subnetDim.subnet_key
rows: 16
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: b2bDim
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 3
ref: dwh.fact.b2bunit_key
rows: 1
Extra: Using where
3 rows in set (0.00 sec)
Is it normal ?