MySQL Forums
Forum List  »  Merge Storage Engine

Merge table performance
Posted by: Alastair Smith
Date: July 19, 2005 09:13AM

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)

Options: ReplyQuote


Subject
Views
Written By
Posted
Merge table performance
9838
July 19, 2005 09:13AM
5103
July 19, 2005 11:37PM
4832
July 20, 2005 12:58AM
4727
July 21, 2005 01:38AM


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.