MySQL Forums
Forum List  »  Merge Storage Engine

merge performance problem
Posted by: Thiago Dias Fascin
Date: December 03, 2007 01:04PM

I'm having a problem when using two merge table in a join, the select became very slow.

Tables a_1, a_2... has each one something like 1 million records.
Tables b_1, b_2... has each one something like five hundred records.

This select is taking 300 seconds:
SELECT
SQL_NO_CACHE count( * )
FROM
temp.a_merge AS a
JOIN temp.b_merge AS b ON b.uniqueid = a.uniqueid
WHERE
a.calldate >= '2007-11-20'
AND a.calldate <= '2007-12-03 23:59:59' ;



Table Structure:

CREATE TABLE `a_1` (
`uniqueID` varchar(32) NOT NULL,
`date_added` datetime NOT NULL,
PRIMARY KEY (`uniqueID`),
KEY `date_added` (`date_added`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `a_2` (
`uniqueID` varchar(32) NOT NULL,
`date_added` datetime NOT NULL,
PRIMARY KEY (`uniqueID`),
KEY `date_added` (`date_added`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `a_3`...
CREATE TABLE `a_4`...
CREATE TABLE `a_5`...
CREATE TABLE `a_6`...

CREATE TABLE `a_merge` (
`uniqueID` varchar(32) NOT NULL,
`date_added` datetime NOT NULL,
PRIMARY KEY (`uniqueID`),
KEY `date_added` (`date_added`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`a_6`,`a_5`,`a_4`,`a_3`,`a_2`,`a_1`);

CREATE TABLE `b_1` (
`uniqueid` varchar(32) NOT NULL,
PRIMARY KEY (`uniqueid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `b_2` (
`uniqueid` varchar(32) NOT NULL,
PRIMARY KEY (`uniqueid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Table structure for table `b_merge` */


CREATE TABLE `b_3`...
CREATE TABLE `b_4`...
CREATE TABLE `b_5`...
CREATE TABLE `b_6`...

CREATE TABLE `b_merge` (
`uniqueid` varchar(32) NOT NULL,
PRIMARY KEY (`uniqueid`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`b_6`,`b_5`,`b_4`,`b_3`,`b_2`,`b_1`);

Somebody can help me?
Tks.

Options: ReplyQuote


Subject
Views
Written By
Posted
merge performance problem
11772
December 03, 2007 01:04PM
6461
December 03, 2007 06:42PM
6671
December 04, 2007 04:33AM
6223
December 04, 2007 07:54PM


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.