Re: Mysql 5.1 to 5.5.1.18 Upgrade Poor Performance
Posted by: Thomas Frederiksen
Date: December 30, 2011 01:30PM
Date: December 30, 2011 01:30PM
Table Definitions:
Table Statii 5.1.51:
Table Statii 5.5.18:
Table Create Table --------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- tblesiids CREATE TABLE `tblesiids` ( `GKEY` int(10) unsigned NOT NULL AUTO_INCREMENT, `ESIID` varchar(40) NOT NULL DEFAULT '', `Tdsp_ID` int(10) unsigned DEFAULT NULL, `Street` varchar(80) NOT NULL DEFAULT '', `City` varchar(30) NOT NULL DEFAULT '', `State` char(2) NOT NULL DEFAULT '', `Zip` varchar(5) DEFAULT NULL, `County` varchar(50) DEFAULT NULL, `Meter_Number` varchar(20) NOT NULL DEFAULT '', `Load_Profile` varchar(80) NOT NULL DEFAULT '', `Manual_Rate_Code` varchar(30) DEFAULT '', `Rate_Code` varchar(30) NOT NULL DEFAULT '', `Read_Cycle` varchar(10) NOT NULL DEFAULT '', `Meter_Type_ID` varchar(5) NOT NULL DEFAULT '', `Life_Support` tinyint(1) unsigned NOT NULL DEFAULT '0', `Has_HUD` tinyint(1) unsigned NOT NULL DEFAULT '0', `ICAP` decimal(11,4) DEFAULT NULL, `transmission_tag` decimal(11,4) DEFAULT NULL, `Stratum_Variable` decimal(11,4) DEFAULT NULL, `Muni_Coop` varchar(10) DEFAULT NULL, `tod_code` int(11) DEFAULT NULL, `RecStatus` tinyint(3) unsigned DEFAULT '1', `RecDate` datetime DEFAULT NULL, `RecUpdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `RecUpdBy` int(10) unsigned NOT NULL DEFAULT '0', `Taxable` varchar(25) DEFAULT NULL, `residential` char(2) DEFAULT NULL, `LBMP_Zone` varchar(50) DEFAULT NULL, `Distribution_loss_factor` varchar(5) DEFAULT NULL, `keyname` varchar(70) DEFAULT '', `commodity` char(1) NOT NULL DEFAULT 'E', `gas_category` int(11) DEFAULT NULL, `gas_flow` int(11) DEFAULT NULL, PRIMARY KEY (`GKEY`), UNIQUE KEY `Unique_on` (`ESIID`,`Tdsp_ID`,`commodity`), KEY `ESIID` (`ESIID`), KEY `Tdsp_ID` (`Tdsp_ID`), KEY `State` (`State`), KEY `Has_HUD` (`Has_HUD`), KEY `Rate_Code` (`Rate_Code`), KEY `Zone` (`LBMP_Zone`), KEY `idx_tdsp_id_rec_date` (`Tdsp_ID`,`RecDate`) ) ENGINE=InnoDB AUTO_INCREMENT=409714 DEFAULT CHARSET=latin1 Table Create Table -------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- xsp_rate_codes CREATE TABLE `xsp_rate_codes` ( `gkey` int(11) unsigned NOT NULL DEFAULT '0', `ldc_rate_class` varchar(30) DEFAULT NULL, `Load_Profile_Description` varchar(30) DEFAULT NULL, `type_of_service_description` varchar(80) DEFAULT NULL, `Standard_Point_Location_Code` varchar(80) DEFAULT NULL, `capacity_obligation_quantity` decimal(11,4) DEFAULT NULL, `transmission_obligation_quantity` decimal(11,4) DEFAULT NULL, `sender_transaction_id` char(40) NOT NULL DEFAULT '', `transaction_date` date DEFAULT NULL, `transaction_time` time DEFAULT NULL, `meter_number` varchar(30) DEFAULT NULL, `is_lighting` tinyint(4) DEFAULT NULL, KEY `gkey` (`gkey`), KEY `transaction_date` (`transaction_date`), KEY `transaction_time` (`transaction_time`), KEY `meter_number` (`meter_number`), KEY `is_lighting` (`is_lighting`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
Table Statii 5.1.51:
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment --------- ------ ------- ---------- ------ -------------- ----------- --------------- ------------ --------- -------------- ------------------- ----------- ---------- ----------------- -------- -------------- ------- tblesiids InnoDB 10 Compact 353760 161 57245696 0 95436800 6291456 409715 2010-10-27 18:57:10 (NULL) (NULL) latin1_swedish_ci (NULL) xsp_rate_codes InnoDB 10 Compact 38772625 96 3752853504 0 4052746240 4194304 (NULL) 2010-10-27 19:04:04 (NULL) (NULL) latin1_swedish_ci (NULL)
Table Statii 5.5.18:
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment --------- ------ ------- ---------- ------ -------------- ----------- --------------- ------------ --------- -------------- ------------------- ----------- ---------- ----------------- -------- -------------- ------- tblesiids InnoDB 10 Compact 364688 156 57245696 0 94404608 7340032 409715 2011-12-06 04:16:18 (NULL) (NULL) latin1_swedish_ci (NULL) xsp_rate_codes InnoDB 10 Compact 37485923 100 3752853504 0 4052746240 4194304 (NULL) 2011-12-06 05:09:03 (NULL) (NULL) latin1_swedish_ci (NULL)
Subject
Views
Written By
Posted
1117
December 27, 2011 10:28AM
1347
December 30, 2011 12:52PM
Re: Mysql 5.1 to 5.5.1.18 Upgrade Poor Performance
1324
December 30, 2011 01:30PM
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.