MySQL Forums
Forum List  »  Merge Storage Engine

Query performance on merge tables after upgrade
Posted by: Valerie Jones
Date: July 18, 2008 05:09PM

Our department is primarily using Sybase, and we are all fairly new MySQL users, so we're dealing with a learning curve. We recently migrated our Data Warehouse data from Sybase to MySQL, with very good results, until we decided to upgrade. We are using the merge storage engine, as we have a large amount of data that we're dealing with.

We recently upgraded our test server from version 4.1.22 to 5.0.51, and our query performance has suffered greatly. I've already double-checked that all tables used in the merge are defined identically to the merge table. The query plans are very different (post-upgrade) when we have queries with joins. These tables are pretty large, each fs_ledgerdetail_YYYY table has between 2 & 4 million rows, and each fs_account_hier_YYYY table has over 100,000 rows.

I have a feeling that we just need to make some changes to our indexes, but I don't know where to start with this problem. Any suggestions would be greatly appreciated. Thanks!

Here is one example of a query:

SELECT dwhm_fs.fs_ledgerdetail.monthend_date AS 'Monthend Date' ,
dwhm_fs.fs_ledgerdetail.account_code AS 'Account Code' ,
dwhm_fs.fs_ledgerdetail.fund_code AS 'Fund Code' ,
dwhm_fs.fs_ledgerdetail.sub_code AS 'Sub Code' ,
dwhm_fs.fs_ledgerdetail.object_code AS 'Object Code' ,
dwhm_fs.fs_ledgerdetail.trans_description AS 'Transaction Description' ,
dwhm_fs.fs_ledgerdetail.trans_amount AS 'Transaction Amount'
FROM dwhm_fs.fs_ledgerdetail, dwhm_fs.fs_account_hier
WHERE (dwhm_fs.fs_ledgerdetail.location_code ='9')
AND (dwhm_fs.fs_account_hier.department_code = '0302')
AND dwhm_fs.fs_ledgerdetail.monthend_date between '2007-1-31' and '2008-1-31'
AND dwhm_fs.fs_ledgerdetail.monthend_date=dwhm_fs.fs_account_hier.monthend_date
AND dwhm_fs.fs_ledgerdetail.location_code=dwhm_fs.fs_account_hier.location_code
AND dwhm_fs.fs_ledgerdetail.account_code=dwhm_fs.fs_account_hier.account_code
ORDER BY dwhm_fs.fs_ledgerdetail.monthend_date

The explain for this SQL in version 4.1.22:
1 SIMPLE fs_account_hier ref PRIMARY,XPKlocation_account,XAKaccount,XAKdepartment,XAKhi XAKdepartment 4 const 848 Using where; Using temporary; Using filesort
1 SIMPLE fs_ledgerdetail ref PRIMARY,XAKfs_detail_indx,XPKfs_ledgerdetail,XAKaccount XAKaccount 9 dwhm_fs.fs_account_hier.account_code,dwhm_fs.fs_account_hier.monthend_date 54 Using where

The explain in version 5.0.51:
SIMPLE fs_ledgerdetail range PRIMARY,XAKfs_detail_indx,XPKfs_ledgerdetail,XAKaccount XPKfs_ledgerdetail 3 <null> 4355427 Using where
SIMPLE fs_account_hier ref PRIMARY,XPKlocation_account,XAKaccount,XAKdepartment,XAKhi XAKaccount 6 dwhm_fs.fs_ledgerdetail.account_code 16 Using where

Each merge table has 14 underlying tables, here are examples of those:

CREATE TABLE `fs_ledgerdetail_9899` (
`monthend_date` date NOT NULL default '0000-00-00',
`dataclass_code` char(1) NOT NULL default '',
`location_code` char(1) NOT NULL default '',
`linear_month` char(3) NOT NULL default '',
`typeentry_code` char(2) NOT NULL default '',
`point_entry` char(4) NOT NULL default '',
`trans_id` char(6) NOT NULL default '',
`sequence` int(11) NOT NULL default '0',
`account_code` char(6) NOT NULL default '',
`cost_center` char(2) NOT NULL default '',
`fund_code` char(5) NOT NULL default '',
`project_code` char(6) NOT NULL default '',
`sub_code` char(2) NOT NULL default '',
`object_code` char(4) NOT NULL default '',
`source_code` char(6) NOT NULL default '',
`doc_date` date NOT NULL default '0000-00-00',
`trans_description` char(20) NOT NULL default '',
`trans_reference` char(10) NOT NULL default '',
`partial_full_flag` char(1) NOT NULL default '',
`trans_amount` decimal(10,2) NOT NULL default '0.00',
`subsystem_id` char(1) NOT NULL default '',
`period_close_flag` char(1) NOT NULL default '',
`trans_close_flag` char(1) NOT NULL default '',
`post_date` date NOT NULL default '0000-00-00',
`final_flag` char(1) NOT NULL default '',
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`monthend_date`,`final_flag`,`location_code`,`typeentry_code`,`point_entry`,`trans_id`,`dataclass_code`,`sequence`),
UNIQUE KEY `XAKfs_detail_indx` (`monthend_date`,`final_flag`,`location_code`,`typeentry_code`,`point_entry`,`trans_id`,`dataclass_code`,`sequence`),
KEY `XPKfs_ledgerdetail_9899` (`monthend_date`,`final_flag`,`account_code`,`fund_code`,`location_code`),
KEY `XAKaccount` (`account_code`,`monthend_date`,`final_flag`,`location_code`),
KEY `XAKfund` (`fund_code`,`monthend_date`,`final_flag`,`location_code`),
KEY `XAKproject` (`project_code`,`monthend_date`,`final_flag`),
KEY `XAKsub` (`sub_code`,`monthend_date`,`final_flag`,`location_code`),
KEY `XAKobject` (`object_code`,`monthend_date`,`final_flag`,`location_code`),
KEY `XAKtypeentry` (`typeentry_code`,`monthend_date`,`final_flag`,`location_code`),
KEY `XAKaccount_dataclass` (`dataclass_code`,`account_code`,`monthend_date`,`final_flag`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `fs_account_hier_9899` (
`monthend_date` date NOT NULL default '0000-00-00',
`location_code` char(1) NOT NULL default '',
`account_code` char(6) NOT NULL default '',
`cost_center` char(2) NOT NULL default '',
`account_title` char(40) NOT NULL default '',
`sub_ledger_flag` char(1) NOT NULL default '',
`sub_ledger_history_flag` char(1) NOT NULL default '',
`sub_ledger_reference_code` char(1) NOT NULL default '',
`dummy_flag` char(1) NOT NULL default '',
`account_group_title1` char(18) NOT NULL default '',
`annual_report_code` char(6) NOT NULL default '',
`uas_code` char(6) NOT NULL default '',
`discipline_code` char(3) NOT NULL default '',
`jag_code` char(6) NOT NULL default '',
`account_group_code` char(6) NOT NULL default '',
`account_group_title2` char(35) NOT NULL default '',
`nsf_code` char(3) NOT NULL default '',
`sau_code` char(1) NOT NULL default '',
`program_code` char(2) NOT NULL default '',
`function_code` char(2) NOT NULL default '',
`account_responsibility` char(20) NOT NULL default '',
`home_department_code` char(6) NOT NULL default '',
`capitalization_code` char(1) NOT NULL default '',
`sub_department` char(8) NOT NULL default '',
`low_level1` char(8) NOT NULL default '',
`low_level2` char(8) NOT NULL default '',
`low_level3` char(8) NOT NULL default '',
`low_level4` char(8) NOT NULL default '',
`bal_sheet_account_closed_flag` char(1) NOT NULL default '',
`asset_number` char(5) default NULL,
`organization_code` char(4) NOT NULL default '',
`organization_title` char(40) default NULL,
`division_code` char(4) NOT NULL default '',
`division_title` char(40) default NULL,
`subdivision_code` char(4) NOT NULL default '',
`subdivision_title` char(40) default NULL,
`department_code` char(4) NOT NULL default '',
`department_title` char(40) default NULL,
PRIMARY KEY (`account_code`,`monthend_date`,`location_code`,`cost_center`),
UNIQUE KEY `XPKlocation_account` (`monthend_date`,`location_code`,`account_code`,`cost_center`),
KEY `XAKaccount` (`account_code`),
KEY `XAKdepartment` (`department_code`),
KEY `XAKfunction` (`function_code`),
KEY `XAKhi` (`department_code`),
KEY `XAKorganization` (`organization_code`),
KEY `XAKdivision` (`division_code`),
KEY `XAKsubdivision` (`subdivision_code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1


The merge table definitions are:

CREATE TABLE `fs_ledgerdetail` (
`monthend_date` date NOT NULL default '0000-00-00',
`dataclass_code` char(1) NOT NULL default '',
`location_code` char(1) NOT NULL default '',
`linear_month` char(3) NOT NULL default '',
`typeentry_code` char(2) NOT NULL default '',
`point_entry` char(4) NOT NULL default '',
`trans_id` char(6) NOT NULL default '',
`sequence` int(11) NOT NULL default '0',
`account_code` char(6) NOT NULL default '',
`cost_center` char(2) NOT NULL default '',
`fund_code` char(5) NOT NULL default '',
`project_code` char(6) NOT NULL default '',
`sub_code` char(2) NOT NULL default '',
`object_code` char(4) NOT NULL default '',
`source_code` char(6) NOT NULL default '',
`doc_date` date NOT NULL default '0000-00-00',
`trans_description` char(20) NOT NULL default '',
`trans_reference` char(10) NOT NULL default '',
`partial_full_flag` char(1) NOT NULL default '',
`trans_amount` decimal(10,2) NOT NULL default '0.00',
`subsystem_id` char(1) NOT NULL default '',
`period_close_flag` char(1) NOT NULL default '',
`trans_close_flag` char(1) NOT NULL default '',
`post_date` date NOT NULL default '0000-00-00',
`final_flag` char(1) NOT NULL default '',
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`monthend_date`,`final_flag`,`location_code`,`typeentry_code`,`point_entry`,`trans_id`,`dataclass_code`,`sequence`),
UNIQUE KEY `XAKfs_detail_indx` (`monthend_date`,`final_flag`,`location_code`,`typeentry_code`,`point_entry`,`trans_id`,`dataclass_code`,`sequence`),
KEY `XPKfs_ledgerdetail` (`monthend_date`,`final_flag`,`account_code`,`fund_code`,`location_code`),
KEY `XAKaccount` (`account_code`,`monthend_date`,`final_flag`,`location_code`),
KEY `XAKfund` (`fund_code`,`monthend_date`,`final_flag`,`location_code`),
KEY `XAKproject` (`project_code`,`monthend_date`,`final_flag`),
KEY `XAKsub` (`sub_code`,`monthend_date`,`final_flag`,`location_code`),
KEY `XAKobject` (`object_code`,`monthend_date`,`final_flag`,`location_code`),
KEY `XAKtypeentry` (`typeentry_code`,`monthend_date`,`final_flag`,`location_code`),
KEY `XAKaccount_dataclass` (`dataclass_code`,`account_code`,`monthend_date`,`final_flag`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST


CREATE TABLE `fs_account_hier` (
`monthend_date` date NOT NULL default '0000-00-00',
`location_code` char(1) NOT NULL default '',
`account_code` char(6) NOT NULL default '',
`cost_center` char(2) NOT NULL default '',
`account_title` char(40) NOT NULL default '',
`sub_ledger_flag` char(1) NOT NULL default '',
`sub_ledger_history_flag` char(1) NOT NULL default '',
`sub_ledger_reference_code` char(1) NOT NULL default '',
`dummy_flag` char(1) NOT NULL default '',
`account_group_title1` char(18) NOT NULL default '',
`annual_report_code` char(6) NOT NULL default '',
`uas_code` char(6) NOT NULL default '',
`discipline_code` char(3) NOT NULL default '',
`jag_code` char(6) NOT NULL default '',
`account_group_code` char(6) NOT NULL default '',
`account_group_title2` char(35) NOT NULL default '',
`nsf_code` char(3) NOT NULL default '',
`sau_code` char(1) NOT NULL default '',
`program_code` char(2) NOT NULL default '',
`function_code` char(2) NOT NULL default '',
`account_responsibility` char(20) NOT NULL default '',
`home_department_code` char(6) NOT NULL default '',
`capitalization_code` char(1) NOT NULL default '',
`sub_department` char(8) NOT NULL default '',
`low_level1` char(8) NOT NULL default '',
`low_level2` char(8) NOT NULL default '',
`low_level3` char(8) NOT NULL default '',
`low_level4` char(8) NOT NULL default '',
`bal_sheet_account_closed_flag` char(1) NOT NULL default '',
`asset_number` char(5) default NULL,
`organization_code` char(4) NOT NULL default '',
`organization_title` char(40) default NULL,
`division_code` char(4) NOT NULL default '',
`division_title` char(40) default NULL,
`subdivision_code` char(4) NOT NULL default '',
`subdivision_title` char(40) default NULL,
`department_code` char(4) NOT NULL default '',
`department_title` char(40) default NULL,
PRIMARY KEY (`account_code`,`monthend_date`,`location_code`,`cost_center`),
UNIQUE KEY `XPKlocation_account` (`monthend_date`,`location_code`,`account_code`,`cost_center`),
KEY `XAKaccount` (`account_code`),
KEY `XAKdepartment` (`department_code`),
KEY `XAKfunction` (`function_code`),
KEY `XAKhi` (`department_code`),
KEY `XAKorganization` (`organization_code`),
KEY `XAKdivision` (`division_code`),
KEY `XAKsubdivision` (`subdivision_code`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`fs_account_hier_0708`,`fs_account_hier_0607`,`fs_account_hier_0506`,`fs_account_hier_0405`,`fs_account_hier_0304`,`fs_account_hier_0203`,`fs_account_hier_0102`,`fs_account_hier_0001`,`fs_account_hier_9900`,`fs_account_hier_9899`,`fs_account_hier_9798`,`fs_account_hier_9697`,`fs_account_hier_9596`,`fs_account_hier_9495`)

Sorry about the length of this message, I want to make sure I provide enough information. Thanks!

Options: ReplyQuote


Subject
Views
Written By
Posted
Query performance on merge tables after upgrade
10402
July 18, 2008 05:09PM


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.