Re: Partition by State

[Rick James]

Well, I have to start with this:
> do you have any other suggestions to imporove query performance?

> PRIMARY KEY (`UUID`),
> has 98 million records and is 38.3G
Ouch. That says that virtually every INSERT is a disk hit -- UUIDs do not cache well.

What's this?
> int(1) default NULL
Is it intended to be a true/false value (which could take only 1 bit)? Well, it is taking 5 _bytes_. INT (regardless of the number after it) is a 4-byte quantity. NULLness takes extra space. Total = 5 bytes (in InnoDB).
TINYINT NOT NULL makes more sense, but still takes 2 bytes (in InnoDB).
BIT(1) NOT NULL may be even better -- 8 fields could fit in 1 byte. (Well, it does for MyISAM; not sure about InnoDB.)
Changing to bit may shrink the 38GB to 19GB. That's not an issue for the disk, but it will eventually be an issue for efficiency of caching data in RAM.

> innodb_buffer_pool_size = 40 G
Good. But, as the table grows, you will hit the UUID issue I mentioned. Changing to bit will delay that issue.

> `STATE` varchar(2) NOT NULL,
Toss the VAR -- STATE abbrevs are always 2 chars.

> `SUPPRESSION` varchar(1) NOT NULL,
Toss the VAR?

> KEY `SUPPRESSION` (`SUPPRESSION`)
Indexes on fields with few values are often never used. Either toss this, or add some more fields to make a "compound" index (if you have need for such).

> query_cache_size = 1 G
> query_cache_limit = 1 G
NO! The Query Cache was designed and implemented when memory was much smaller. With 1G, you have seriously long pauses when it inefficiently does its maintenance. Recommend no more than 50M.

> well as identifying records that are marked with a '1' in the different "business lines" ie, Automotive, Insurance, Education etc...
Eh? Does this mean only one of the bits will be on? Or does it mean that a row could have any mixture of them on?
Read about ENUM (if only one can be on), and SET (if multiple can be on). Those would be even better than lots of BIT(1). And, once you get used to it, they would be more logical.

> If you don't think partitioning would be a good option for this
Please show us the actual queries. Simply partitioning on STATE will do nothing toward optimization of "WHERE state='AL'" -- I need to see the rest of the query/queries, to know whether there is AND... or something else that impacts the index/partitioning.