MySQL Forums
Forum List  »  Partitioning

How should I partition this table?
Posted by: Ron Bergin
Date: April 26, 2013 09:18AM

This is my first experience with partitioning as well as managing a db at this level.

Breif background. We recently migrated our pervasive db to mysql. In that process we (I should say others in our dept) partitioned it by hash on the date field. innodb_file_per_table was not enabled, so all of the tablespace data is in ibdata1. The table has over 1 billion rows and ibdata1 is 800GB.

I'm currently in the process of copying the db to a test server to test different partitioning schemes and have enabled innodb_file_per_table. The structure of the table is as follows.

INVITMD | CREATE TABLE `INVITMD` (
`Division` smallint(5) unsigned DEFAULT NULL,
`InvNum` int(10) unsigned DEFAULT NULL,
`InvLine` smallint(5) unsigned DEFAULT NULL,
`PLU` int(10) unsigned DEFAULT NULL,
`UPC` varchar(14) DEFAULT NULL,
`Qty` smallint(6) DEFAULT NULL,
`ItemPrice` double DEFAULT NULL,
`PriceCode` char(1) DEFAULT NULL,
`NetPrice` double DEFAULT NULL,
`DiscountFlag` char(1) DEFAULT NULL,
`Taxable` char(1) DEFAULT NULL,
`ComShr` char(1) DEFAULT NULL,
`QotNum` int(10) unsigned DEFAULT NULL,
`QotVer` smallint(5) unsigned DEFAULT NULL,
`QotLine` smallint(5) unsigned DEFAULT NULL,
`PrimaryAscID` int(10) unsigned DEFAULT NULL,
`ComSplDate` date DEFAULT NULL,
`ComSplTime` time DEFAULT NULL,
`SplitAscID` int(10) unsigned DEFAULT NULL,
`ItemCost` double DEFAULT NULL,
`InvDate` date DEFAULT NULL,
`Expansion` text,
UNIQUE KEY `Div_date_Line` (`Division`,`InvDate`,`InvNum`,`InvLine`),
KEY `PluNum` (`Division`,`PLU`),
KEY `Tax` (`Division`,`Taxable`),
KEY `UPC` (`Division`,`UPC`),
KEY `PrimaryAsc` (`Division`,`PrimaryAscID`),
KEY `SplitAsc` (`Division`,`SplitAscID`),
KEY `Div_Plu_inv` (`Division`,`PLU`,`InvNum`),
KEY `Div_Date_Plu` (`Division`,`InvDate`,`PLU`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (YEAR(invdate))
PARTITIONS 10 */


My initial plan is to partition by range or list on the Division field giving me between 36 and 40 partitions. I'm also considering sub partitioning on the InvDate field. I'd like to do that by range as well, but from my reading subpartitions don't support "by range". Is that correct?

Does my plan sound like the best approach, or can you recommend something better?

Options: ReplyQuote


Subject
Views
Written By
Posted
How should I partition this table?
3512
April 26, 2013 09:18AM


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.