Hi All,
I am working on a data warehousing project for business intelligence application. We have several aggregate tables in our database and we are planning to create partitions on date_id column which is of `DATE` data type. All the aggregate tables are designed to hold two years worth of data. On average each table consists of 10-15 million records.
I mainly thought of two methods for creating partitions on date_id columns:
Method 1 : PARTITION BY RANGE(date_id)
As partition by range only supports columns of integer type. I would convert the date_id(DATE) into integer value using TO_DAYS function for creating partitioning key and threshold values as shown below.
Table structure before partitioning:
CREATE TABLE `fact_agg_combo_report_day` (
`BrandID` varchar(4) DEFAULT NULL,
`RegionID` varchar(4) DEFAULT NULL,
`RegionName` varchar(25) DEFAULT NULL,
`DistrictID` varchar(4) DEFAULT NULL,
`DistrictName` varchar(25) DEFAULT NULL,
`StoreID` varchar(4) DEFAULT NULL,
`StoreDescription` varchar(50) DEFAULT NULL,
`MerchComboID` int(11) DEFAULT NULL,
`MerchComboDesc` varchar(255) DEFAULT NULL,
`ChannelCode` char(1) DEFAULT NULL,
`DATE_ID` date DEFAULT NULL,
`DATE_ID_YESTERDAY` date DEFAULT NULL,
`DayNameYesterday` varchar(9) DEFAULT NULL,
`DATE_ID_LY` date DEFAULT NULL,
`DAY_OF_WEEK` int(11) DEFAULT NULL,
`DayName` varchar(9) DEFAULT NULL,
`ComboType` varchar(5) DEFAULT NULL,
`SalesAmount` decimal(10,4) DEFAULT NULL,
`SalesAmountLocal` decimal(10,4) DEFAULT NULL,
`ItemQuantity` bigint(11) DEFAULT NULL,
`SalesAmountLY` decimal(10,4) DEFAULT NULL,
`SalesAmountLocalLY` decimal(10,4) DEFAULT NULL,
`ItemQuantityLY` bigint(11) DEFAULT NULL,
`PercentToLY` decimal(19,8) DEFAULT NULL,
`PlanSalesAmount` decimal(12,4) DEFAULT NULL,
`PlanSalesUnits` bigint(11) DEFAULT NULL,
`PercentToPlan` decimal(21,8) DEFAULT NULL,
KEY `ix1_fact_agg_combo_report_day` (`BrandID`,`StoreID`,`DATE_ID`,`MerchComboID`),
KEY `ix_fact_agg_combo_report_day` (`DATE_ID`)
)
Table structure after partitioning will look like :
CREATE TABLE `fact_agg_combo_report_day_saj` (
`BrandID` varchar(4) DEFAULT NULL,
`RegionID` varchar(4) DEFAULT NULL,
`RegionName` varchar(25) DEFAULT NULL,
`DistrictID` varchar(4) DEFAULT NULL,
`DistrictName` varchar(25) DEFAULT NULL,
`StoreID` varchar(4) DEFAULT NULL,
`StoreDescription` varchar(50) DEFAULT NULL,
`MerchComboID` int(11) DEFAULT NULL,
`MerchComboDesc` varchar(255) DEFAULT NULL,
`ChannelCode` char(1) DEFAULT NULL,
`DATE_ID` date DEFAULT NULL,
`DATE_ID_YESTERDAY` date DEFAULT NULL,
`DayNameYesterday` varchar(9) DEFAULT NULL,
`DATE_ID_LY` date DEFAULT NULL,
`DAY_OF_WEEK` int(11) DEFAULT NULL,
`DayName` varchar(9) DEFAULT NULL,
`ComboType` varchar(5) DEFAULT NULL,
`SalesAmount` decimal(10,4) DEFAULT NULL,
`SalesAmountLocal` decimal(10,4) DEFAULT NULL,
`ItemQuantity` bigint(11) DEFAULT NULL,
`SalesAmountLY` decimal(10,4) DEFAULT NULL,
`SalesAmountLocalLY` decimal(10,4) DEFAULT NULL,
`ItemQuantityLY` bigint(11) DEFAULT NULL,
`PercentToLY` decimal(19,8) DEFAULT NULL,
`PlanSalesAmount` decimal(12,4) DEFAULT NULL,
`PlanSalesUnits` bigint(11) DEFAULT NULL,
`PercentToPlan` decimal(21,8) DEFAULT NULL,
KEY `ix1_fact_agg_combo_report_day` (`BrandID`,`StoreID`,`DATE_ID`,`MerchComboID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (TO_DAYS(date_id))
(PARTITION pmin VALUES LESS THAN (0) ENGINE = InnoDB,
PARTITION p201312 VALUES LESS THAN (735599) ENGINE = InnoDB,
PARTITION p201401 VALUES LESS THAN (735630) ENGINE = InnoDB,
PARTITION p201402 VALUES LESS THAN (735658) ENGINE = InnoDB,
PARTITION p201403 VALUES LESS THAN (735689) ENGINE = InnoDB,
PARTITION p201404 VALUES LESS THAN (735719) ENGINE = InnoDB,
PARTITION p201405 VALUES LESS THAN (735750) ENGINE = InnoDB,
PARTITION p201406 VALUES LESS THAN (735780) ENGINE = InnoDB,
PARTITION p201407 VALUES LESS THAN (735811) ENGINE = InnoDB,
PARTITION p201408 VALUES LESS THAN (735842) ENGINE = InnoDB,
PARTITION p201409 VALUES LESS THAN (735872) ENGINE = InnoDB,
PARTITION p201410 VALUES LESS THAN (735903) ENGINE = InnoDB,
PARTITION p201411 VALUES LESS THAN (735933) ENGINE = InnoDB,
PARTITION p201412 VALUES LESS THAN (735964) ENGINE = InnoDB,
PARTITION p201501 VALUES LESS THAN (735995) ENGINE = InnoDB,
PARTITION p201502 VALUES LESS THAN (736023) ENGINE = InnoDB,
PARTITION p201503 VALUES LESS THAN (736054) ENGINE = InnoDB,
PARTITION p201504 VALUES LESS THAN (736084) ENGINE = InnoDB,
PARTITION p201505 VALUES LESS THAN (736115) ENGINE = InnoDB,
PARTITION p201506 VALUES LESS THAN (736145) ENGINE = InnoDB,
PARTITION p201507 VALUES LESS THAN (736176) ENGINE = InnoDB,
PARTITION p201508 VALUES LESS THAN (736207) ENGINE = InnoDB,
PARTITION p201509 VALUES LESS THAN (736237) ENGINE = InnoDB,
PARTITION p201510 VALUES LESS THAN (736268) ENGINE = InnoDB,
PARTITION p201511 VALUES LESS THAN (736298) ENGINE = InnoDB,
PARTITION p201512 VALUES LESS THAN (736329) ENGINE = InnoDB,
PARTITION p201601 VALUES LESS THAN (736360) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
Method2: PARTITION BY RANGE COLUMNS
As this partitioning type supports DATE type along with integers I can use this method directly specifying the column name, value as the partitioning key and threshold for the partition respectively. The issue with this method is to deciding the threshold for min partition. I have selected the min date as `0000-01-01` as we never going to have any records with that date or closer to that date.
Table Structure after partitioning:
CREATE TABLE `fact_agg_combo_report_day` (
`BrandID` varchar(4) DEFAULT NULL,
`RegionID` varchar(4) DEFAULT NULL,
`RegionName` varchar(25) DEFAULT NULL,
`DistrictID` varchar(4) DEFAULT NULL,
`DistrictName` varchar(25) DEFAULT NULL,
`StoreID` varchar(4) DEFAULT NULL,
`StoreDescription` varchar(50) DEFAULT NULL,
`MerchComboID` int(11) DEFAULT NULL,
`MerchComboDesc` varchar(255) DEFAULT NULL,
`ChannelCode` char(1) DEFAULT NULL,
`DATE_ID` date DEFAULT NULL,
`DATE_ID_YESTERDAY` date DEFAULT NULL,
`DayNameYesterday` varchar(9) DEFAULT NULL,
`DATE_ID_LY` date DEFAULT NULL,
`DAY_OF_WEEK` int(11) DEFAULT NULL,
`DayName` varchar(9) DEFAULT NULL,
`ComboType` varchar(5) DEFAULT NULL,
`SalesAmount` decimal(10,4) DEFAULT NULL,
`SalesAmountLocal` decimal(10,4) DEFAULT NULL,
`ItemQuantity` bigint(11) DEFAULT NULL,
`SalesAmountLY` decimal(10,4) DEFAULT NULL,
`SalesAmountLocalLY` decimal(10,4) DEFAULT NULL,
`ItemQuantityLY` bigint(11) DEFAULT NULL,
`PercentToLY` decimal(19,8) DEFAULT NULL,
`PlanSalesAmount` decimal(12,4) DEFAULT NULL,
`PlanSalesUnits` bigint(11) DEFAULT NULL,
`PercentToPlan` decimal(21,8) DEFAULT NULL,
KEY `ix1_fact_agg_combo_report_day` (`BrandID`,`StoreID`,`DATE_ID`,`MerchComboID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE COLUMNS(date_id)
(PARTITION pmin VALUES LESS THAN ('0000-01-01') ENGINE = InnoDB,
PARTITION p201403 VALUES LESS THAN ('2014-04-01') ENGINE = InnoDB,
PARTITION p201404 VALUES LESS THAN ('2014-05-01') ENGINE = InnoDB,
PARTITION p201405 VALUES LESS THAN ('2014-06-01') ENGINE = InnoDB,
PARTITION p201406 VALUES LESS THAN ('2014-07-01') ENGINE = InnoDB,
PARTITION p201407 VALUES LESS THAN ('2014-08-01') ENGINE = InnoDB,
PARTITION p201408 VALUES LESS THAN ('2014-09-01') ENGINE = InnoDB,
PARTITION p201409 VALUES LESS THAN ('2014-10-01') ENGINE = InnoDB,
PARTITION p201410 VALUES LESS THAN ('2014-11-01') ENGINE = InnoDB,
PARTITION p201411 VALUES LESS THAN ('2014-12-01') ENGINE = InnoDB,
PARTITION p201412 VALUES LESS THAN ('2015-01-01') ENGINE = InnoDB,
PARTITION p201501 VALUES LESS THAN ('2015-02-01') ENGINE = InnoDB,
PARTITION p201502 VALUES LESS THAN ('2015-03-01') ENGINE = InnoDB,
PARTITION p201503 VALUES LESS THAN ('2015-04-01') ENGINE = InnoDB,
PARTITION p201504 VALUES LESS THAN ('2015-05-01') ENGINE = InnoDB,
PARTITION p201505 VALUES LESS THAN ('2015-06-01') ENGINE = InnoDB,
PARTITION p201506 VALUES LESS THAN ('2015-07-01') ENGINE = InnoDB,
PARTITION p201507 VALUES LESS THAN ('2015-08-01') ENGINE = InnoDB,
PARTITION p201508 VALUES LESS THAN ('2015-09-01') ENGINE = InnoDB,
PARTITION p201509 VALUES LESS THAN ('2015-10-01') ENGINE = InnoDB,
PARTITION p201510 VALUES LESS THAN ('2015-11-01') ENGINE = InnoDB,
PARTITION p201511 VALUES LESS THAN ('2015-12-01') ENGINE = InnoDB,
PARTITION p201512 VALUES LESS THAN ('2016-01-01') ENGINE = InnoDB,
PARTITION p201601 VALUES LESS THAN ('2016-02-01') ENGINE = InnoDB,
PARTITION p201602 VALUES LESS THAN ('2016-03-01') ENGINE = InnoDB,
PARTITION p201603 VALUES LESS THAN ('2016-04-01') ENGINE = InnoDB,
PARTITION p201604 VALUES LESS THAN ('2016-05-01') ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)
Please let me know which method would be appropriate to use. Potential issues that may occur by using either of these methods if any. Would there be any performance issues with partitioning?