Partition by State
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