MySQL Forums
Forum List  »  Partitioning

Large Table Architecture
Posted by: Darren Mitchell
Date: May 24, 2011 12:55PM

Greetings,

I am a DBA with more of a background in MS SQL Server breaking into the MySQL space. I am currently working on a project consisting of 500 mil. rows and growing. I have tried partitioning this table a couple of different ways to improve the performance while trying to generate aggregate counts from it. I'm wondering if this is the best approach for this volume of data. The way it runs with sorting etc...uses too much temp and stalls out.

CREATE TABLE `RESP_CA_MasterSent_split` (
	`Email` VARCHAR(125) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
	`Campaign` VARCHAR(75) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
	`Action` VARCHAR(15) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
	`Description` VARCHAR(75) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
	`Timestamp` DATETIME NULL DEFAULT NULL,
	`LaunchID` INT(10) NULL DEFAULT NULL,
	`OPID` VARCHAR(15) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
	`InFile` VARCHAR(25) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
	INDEX `IDX_MasterSent_CampAction` (`Campaign`, `Action`)
)
COLLATE='latin1_general_ci'
ENGINE=MyISAM;

Version: 5.5.10

I've tried partitioning on MONTH(TimeStamp) = 12 partitions
and
LIST COLUMN(Col 3)

The table consists of 7 columns
Col 1 varchar(125) email
Col 2 varchar(75) 3000 +values
Col 3 varchar(15) 3 possible values
Col 4 varchar(75) description
Col 5 datetime timestamp from file
Col 6 int non unique ID
Col 7 varchar - InFile - self generated value based on the file data was loaded from


Any suggestions on what I could do differently, tips on how to optimize large data set in MySQL...

Many thanks in advance.



Edited 1 time(s). Last edit at 05/24/2011 01:33PM by Darren Mitchell.

Options: ReplyQuote


Subject
Views
Written By
Posted
Large Table Architecture
4534
May 24, 2011 12:55PM
2603
May 25, 2011 07:45PM
1495
May 25, 2011 09:33PM
1570
May 25, 2011 11:31PM
1327
May 26, 2011 09:49AM
1722
May 26, 2011 10:52AM


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.