MySQL Forums :: Partitioning :: Large Table Architecture

Advanced Search

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


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',
	`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`)

Version: 5.5.10

I've tried partitioning on MONTH(TimeStamp) = 12 partitions

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 4436 Darren Mitchell 05/24/2011 12:55PM
Re: Large Table Architecture 2549 Rick James 05/25/2011 07:45PM
Re: Large Table Architecture 1469 Darren Mitchell 05/25/2011 09:33PM
Re: Large Table Architecture 1548 Rick James 05/25/2011 11:31PM
Re: Large Table Architecture 1306 Darren Mitchell 05/26/2011 09:49AM
Re: Large Table Architecture 1699 Rick James 05/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.