Re: Modify primary key for partitioning
Hi,
Here is the table's structure :
CREATE TABLE `My_Table` (
`ID_MSG` varchar(80) CHARACTER SET latin1 NOT NULL ,
`NUM_CERTIFICATE` int(10) unsigned DEFAULT NULL ,
`XMLCERTIFICATE` longtext CHARACTER SET latin1 ,
`XMLPOSTIT` mediumtext CHARACTER SET latin1 ,
`ID_CONVERSATION` varchar(80) CHARACTER SET latin1 NOT NULL ,
`ID_VERSION_XML` smallint(5) unsigned NOT NULL ,
`SENT_DATE` datetime NOT NULL ,
`RECEIVED_DATE` datetime NOT NULL ,
`REMINDER_DATE` datetime DEFAULT NULL ,
`TREATMENT_DATE` datetime DEFAULT NULL ,
`TREATMENT_STATE` smallint(5) unsigned NOT NULL DEFAULT '1' ,
`INTEGRATION_CHECK` char(3) CHARACTER SET latin1 DEFAULT NULL ,
`CHECKED_RESULT` char(3) CHARACTER SET latin1 DEFAULT NULL ,
`RETURNED_CONTENTS` text CHARACTER SET latin1 ,
`ID_LRA` int(10) unsigned DEFAULT NULL ,
`D_MUTATION_DATE_CERTIFICATE` date DEFAULT NULL,
`S_TYPE_GOODS_CERTIFICATE` char(2) DEFAULT NULL,
PRIMARY KEY (`ID_MSG`,`RECEIVED_DATE`),
KEY `Idx_NUM_CERTIFICATE` (`NUM_CERTIFICATE`) USING BTREE,
KEY `Idx_ID_CONVERSATION` (`ID_CONVERSATION`) USING BTREE,
KEY `Idx_TREATMENT_STATE` (`TREATMENT_STATE`) USING BTREE,
KEY `Idx_CHECKED_RESULT` (`CHECKED_RESULT`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16
/*!50500 PARTITION BY RANGE COLUMNS(RECEIVED_DATE)
(PARTITION p201101 VALUES LESS THAN ('2011-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p201106 VALUES LESS THAN ('2011-06-01 00:00:00') ENGINE = InnoDB,
PARTITION p201201 VALUES LESS THAN ('2012-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p201206 VALUES LESS THAN ('2012-06-01 00:00:00') ENGINE = InnoDB,
PARTITION p201301 VALUES LESS THAN ('2013-01-01 00:00:00') ENGINE = InnoDB,
PARTITION p201306 VALUES LESS THAN ('2013-06-01 00:00:00') ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)
* How many rows in the table (eventually)
There are currently 300,000 rows in the table.
* Description of how rows are inserted (batched, LOAD, etc)
This table is mostly used with batchs. Around 15,000 inserts per Month this year, but it's gonna increase to 60,000 per Month shorthly.
* Whether you DELETE rows, especially if you delete based on age.
No Delete on this table. We want to keep everything. That's why I'm interested in partitioning and compressing (we use many TEXT fields in this table). Partitioning is also interesting as we backup the database using a filesystem copy (we first shutdown the database). So, I guess that all the past quarters (partitions no longer used for DML) won't be copied anymore (synchronisation is used). Most of the time, we will only use the current partition, so that there will be less datas to copy (I hope to save time for backing up since the table is going to get huge)
* What the SELECTs look like.
Most of the selects are based on the "received_date" (BETWEEN ... AND ...), and occasionnaly add some others conditions. But "received_date" is definitly the most selected column (I hope to gain some performance by partitioning on this column)
By the way, should I create the PK on (`RECEIVED_DATE`,`ID_MSG`) instead of (`ID_MSG`,`RECEIVED_DATE`), so that the query on "received_date" can use the PK index ? (I'm not sure the PK is used if ID_MSG is the first column in the index)
PS : The date format is YYYY-MM-DD
Thank you for your link !
Regards,