Merge table performance
Hi,
I've created a merge table using two identical base tables, but see very different performance characteristics, when querying data contained within the second table in the merge table definition.
Query 1 on base table 1 returns in 8 seconds
Query 1 on merge table returns in 8 seconds
Query 2 on base table 2 returns in 8 seconds
Query 2 on merge table returns in 22 seconds
Then when I change the order of the tables in the union clause, such that
UNION=(table2,table1), the performance of the merge table queries alters such that;
Query 1 on merge table returns in 22 seconds
Query 2 on merge table returns in 8 seconds
Am I running into server variable constraint here, due to the combined size of both tables/indexes? If so, I was wondering which server variables in MySQL version 4.0.20 may need tuning in order to support the use of merge tables?
Thanks,
Alastair.
I'm creating the merge table by;
drop table if exists WING.PERFORMANCE_MERGE;
CREATE TABLE WING.`PERFORMANCE_MERGE` (
`ID` int(11) NOT NULL default '0',
`ORGANISATION_ID` int(11) NOT NULL default '0',
`WEEKNUMBER` tinyint(2) NOT NULL default '0',
`YEARCODE` char(2) NOT NULL default '',
`PERIOD` tinyint(2) NOT NULL default '0',
`TARGETCODE` varchar(20) default NULL,
`ACTUAL` decimal(11,2) default NULL,
KEY (`ID`),
KEY `PERFORMANCE_ORGANISATION_ID_IDX` (`ORGANISATION_ID`),
KEY `PERFORMANCE_WEEKNUMBER_IDX` (`WEEKNUMBER`),
KEY `PERFORMANCE_YEARCODE_IDX` (`YEARCODE`),
KEY `PERFORMANCE_PERIOD_IDX` (`PERIOD`),
KEY `PERFORMANCE_TARGETCODE_IDX` (`TARGETCODE`)
) TYPE=MERGE UNION=(PERFORMANCE_05,PERFORMANCE_06) INSERT_METHOD=FIRST;
The underlying tables are both compressed;
Status of base table 1
Name: PERFORMANCE_05
Type: MyISAM
Row_format: Compressed
Rows: 16418740
Avg_row_length: 10
Data_length: 168666072
Max_data_length: 4294967295
Index_length: 617458688
Data_free: 0
Auto_increment: NULL
Create_time: 2005-05-16 11:22:12
Update_time: 2005-07-18 14:38:24
Check_time: 2005-05-16 11:56:36
Create_options:
Comment:
Status of base table 2
Name: PERFORMANCE_06
Type: MyISAM
Row_format: Compressed
Rows: 16418740
Avg_row_length: 10
Data_length: 168666072
Max_data_length: 4294967295
Index_length: 617458688
Data_free: 0
Auto_increment: NULL
Create_time: 2005-07-18 14:05:16
Update_time: 2005-07-18 16:16:59
Check_time: 2005-07-18 17:01:26
Create_options:
Comment:
Status of merge table
Name: PERFORMANCE_MERGE
Type: MRG_MyISAM
Row_format: Dynamic
Rows: 32837480
Avg_row_length: 46
Data_length: 337332144
Max_data_length: NULL
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Create_options:
Comment:
Description of base table 1
mysql> desc PERFORMANCE_05;
Field Type Null Key Default Extra
ID int(11) PRI 0
ORGANISATION_ID int(11) MUL 0
WEEKNUMBER tinyint(2) MUL 0
YEARCODE char(2) MUL
PERIOD tinyint(2) MUL 0
TARGETCODE varchar(20) YES MUL NULL
ACTUAL decimal(11,2) YES NULL
Description of base table 2
mysql> desc PERFORMANCE_06;
Field Type Null Key Default Extra
ID int(11) PRI 0
ORGANISATION_ID int(11) MUL 0
WEEKNUMBER tinyint(2) MUL 0
YEARCODE char(2) MUL
PERIOD tinyint(2) MUL 0
TARGETCODE varchar(20) YES MUL NULL
ACTUAL decimal(11,2) YES NULL
Description of merge table
mysql> desc PERFORMANCE_MERGE;
Field Type Null Key Default Extra
ID int(11) MUL 0
ORGANISATION_ID int(11) MUL 0
WEEKNUMBER tinyint(2) MUL 0
YEARCODE char(2) MUL
PERIOD tinyint(2) MUL 0
TARGETCODE varchar(20) YES MUL NULL
ACTUAL decimal(11,2) YES NULL
I know that the query performance is poor and will be improved by using combined indexes here;
Explain plan for query1 on base table1
mysql> explain
-> select performanc0_.WEEKNUMBER as x0_0_
-> from PERFORMANCE_05 performanc0_
-> where (performanc0_.YEARCODE='05' )
-> and(performanc0_.PERIOD=8 )
-> and(performanc0_.WEEKNUMBER=0 ) limit 1 \G
*************************** 1. row ***************************
table: performanc0_
type: ref
possible_keys: PERFORMANCE_WEEKNUMBER_IDX,PERFORMANCE_YEARCODE_IDX,PERFORMANCE_PERIOD_IDX
key: PERFORMANCE_PERIOD_IDX
key_len: 1
ref: const
rows: 1705920
Extra: Using where
1 row in set (0.10 sec)
Explain plan for query2 on base table2
mysql> explain
-> select performanc0_.WEEKNUMBER as x0_0_
-> from PERFORMANCE_06 performanc0_
-> where (performanc0_.YEARCODE='06' )
-> and(performanc0_.PERIOD=8 )
-> and(performanc0_.WEEKNUMBER=0 ) limit 1 \G
*************************** 1. row ***************************
table: performanc0_
type: ref
possible_keys: PERFORMANCE_WEEKNUMBER_IDX,PERFORMANCE_YEARCODE_IDX,PERFORMANCE_PERIOD_IDX
key: PERFORMANCE_PERIOD_IDX
key_len: 1
ref: const
rows: 1705920
Extra: Using where
1 row in set (0.11 sec)
Explain plan for query1 on merge table
mysql> explain
-> select performanc0_.WEEKNUMBER as x0_0_
-> from PERFORMANCE_MERGE performanc0_
-> where (performanc0_.YEARCODE='05' )
-> and(performanc0_.PERIOD=8 )
-> and(performanc0_.WEEKNUMBER=0 ) limit 1 \G
*************************** 1. row ***************************
table: performanc0_
type: ref
possible_keys: PERFORMANCE_WEEKNUMBER_IDX,PERFORMANCE_YEARCODE_IDX,PERFORMANCE_PERIOD_IDX
key: PERFORMANCE_PERIOD_IDX
key_len: 1
ref: const
rows: 3411840
Extra: Using where
1 row in set (0.00 sec)
Explain plan for query2 on merge table
mysql> explain
-> select performanc0_.WEEKNUMBER as x0_0_
-> from PERFORMANCE_MERGE performanc0_
-> where (performanc0_.YEARCODE='06' )
-> and(performanc0_.PERIOD=8 )
-> and(performanc0_.WEEKNUMBER=0 ) limit 1 \G
*************************** 1. row ***************************
table: performanc0_
type: ref
possible_keys: PERFORMANCE_WEEKNUMBER_IDX,PERFORMANCE_YEARCODE_IDX,PERFORMANCE_PERIOD_IDX
key: PERFORMANCE_PERIOD_IDX
key_len: 1
ref: const
rows: 3411840
Extra: Using where
1 row in set (0.00 sec)
Query 1 on base table 1
mysql> select performanc0_.WEEKNUMBER as x0_0_
-> from PERFORMANCE_05 performanc0_
-> where (performanc0_.YEARCODE='05' )
-> and(performanc0_.PERIOD=8 )
-> and(performanc0_.WEEKNUMBER=0 ) limit 1 \G
*************************** 1. row ***************************
x0_0_: 0
1 row in set (8.22 sec)
Query 2 on base table 2
mysql> select performanc0_.WEEKNUMBER as x0_0_
-> from PERFORMANCE_06 performanc0_
-> where (performanc0_.YEARCODE='06' )
-> and(performanc0_.PERIOD=8 )
-> and(performanc0_.WEEKNUMBER=0 ) limit 1 \G
*************************** 1. row ***************************
x0_0_: 0
1 row in set (8.21 sec)
Query 1 on merge table
mysql> select performanc0_.WEEKNUMBER as x0_0_
-> from PERFORMANCE_MERGE performanc0_
-> where (performanc0_.YEARCODE='05' )
-> and(performanc0_.PERIOD=8 )
-> and(performanc0_.WEEKNUMBER=0 ) limit 1 \G
*************************** 1. row ***************************
x0_0_: 0
1 row in set (8.22 sec)
Query 2 on merge table
mysql> select performanc0_.WEEKNUMBER as x0_0_
-> from PERFORMANCE_MERGE performanc0_
-> where (performanc0_.YEARCODE='06' )
-> and(performanc0_.PERIOD=8 )
-> and(performanc0_.WEEKNUMBER=0 ) limit 1 \G
*************************** 1. row ***************************
x0_0_: 0
1 row in set (21.88 sec)