Large Table Architecture

[Darren Mitchell]

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.