MySQL Forums
Forum List  »  Partitioning

Partition by State
Posted by: Dustin Teter
Date: July 11, 2011 01:23PM

Hello, I'm fairly new to MySQL and have a question about partitioning. I have a table with the following constuct

CREATE TABLE `MASTER_LOOKUP_MATRIX` (
`UUID` varchar(36) NOT NULL,
`EMAIL` varchar(100) NOT NULL,
`STATE` varchar(2) NOT NULL,
`AUTOMOTIVE` int(1) default NULL,
`BIZOP` int(1) default NULL,
`INSURANCE` int(1) default NULL,
`PAYDAY` int(1) default NULL,
`EDUCATION` int(1) default NULL,
`CONSUMER` int(1) default NULL,
`SUPPRESSION` varchar(1) NOT NULL,
`COREG` int(1) NOT NULL,
`FINANCIAL` int(1) NOT NULL,
`DEBT` int(1) NOT NULL,
`DNS_VERIFIED` int(1) NOT NULL,
`TRAVEL` int(1) NOT NULL,
`INVESTOR` int(1) NOT NULL,
`SURVEY` int(1) NOT NULL,
`BUSINESS` int(1) NOT NULL,
`REALTOR` int(1) NOT NULL,
`HOMEOWNER` int(1) NOT NULL,
`RTRN_DT` date NOT NULL,
PRIMARY KEY (`UUID`),
KEY `EMAIL` (`EMAIL`),
KEY `STATE` (`STATE`),
KEY `SUPPRESSION` (`SUPPRESSION`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='New InnoDB Version of Master Lookup - 2011-07-08';

This table currently has 98 million records and is 38.3G. This table is expected to grow by approxiamtly 500k per day.

Current Server config;
48G of memory,
4.5 Terabyte hardrive space

Cache Settings;
innodb_buffer_pool_size = 40 G
key_buffer_size = 256 M
query_cache_size = 1 G
query_cache_limit = 1 G
sort_buffer_size = 32 M
read_rnd_buffer_size = 16 M
table_cache = 2048
read_buffer_size = 4 M

My questions is for performance purposes would I benefit from Partiioning this table? I would like to partition it by STATE... Most of our queires on this table are by STATE and ZIP as well as identifying records that are marked with a '1' in the different "business lines" ie, Automotive, Insurance, Education etc...
If you don't think partitioning would be a good option for this, do you have any other suggestions to imporove query performance?

Thanks for any suggestions!
dt

Options: ReplyQuote


Subject
Views
Written By
Posted
Partition by State
4193
July 11, 2011 01:23PM
1895
July 14, 2011 01:16AM
1632
September 24, 2011 10:45AM


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.