MySQL Forums
Forum List  »  Partitioning

Partitioning by date, and maintaining uniqueness
Posted by: IGG t
Date: August 13, 2013 09:51AM

I have a table which has recently exceeded 1.5M rows, and so am looking at the possibility of partitioning it.
At present we run a 'cleanup' script at the start of the month which deletes everything older than 6 months. But this script is increasingly taking longer and longer to run.

Partitioning seems a perfect solution, as we can partition by month (hopefully making selects quicker), but also because we can simply drop the relevant partition at the start of each month, quickly and cleanly.

However, from my reading this doesn't appear to be possible, so I wanted to check.

We have an ID column [`ID` INT (Primary Key, Auto Inc, Not NUll)], and a created column [`created` Datetime] which is filled in by the web app when a new entry is created (not always the same time that it is added to the Database).

e.g.
CREATE TABLE newTable (
`id` INT NOT NULL AUTO_INCREMENT,
`created` DATETIME NULL,
PRIMARY KEY (`id`)
);

INSERT INTO newTable VALUES (1,'2013-08-13 16:30:00');


But to partition the data by 'created' I would need change the Primary Key to include the 'created' column.

ALTER TABLE newTable
CHANGE COLUMN `Created` `Created` DATETIME NOT NULL ,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`ID`, `Created`);


This however, leaves us open to inadvertantly creating duplicate ID's.

INSERT INTO newTable VALUES (1,'2013-08-13 16:35:01'); #1 Row(s) affected
SELECT COUNT(*) FROM newTable WHERE ID = 1; #2


I could create a UNIQUE Index on the ID column to fix this:

ALTER TABLE newTable
ADD UNIQUE INDEX `ID_Unique` (`ID`);
INSERT INTO newTable VALUES (1,'2013-08-13 16:40:02'); #Error Code: 1062. Duplicate entry '1' for key 'ID_Unique'


But then I can't create the partitions.

ALTER TABLE newTable
PARTITION BY RANGE (TO_DAYS(`Created`)) (
PARTITION p0 VALUES LESS THAN (TO_DAYS('2013-06-01 00:00:00')),
PARTITION p1 VALUES LESS THAN (TO_DAYS('2012-07-01 00:00:00')),
PARTITION p2 VALUES LESS THAN (TO_DAYS('2013-08-01 00:00:00')),
PARTITION p3 VALUES LESS THAN MAXVALUE
);


So as far as I can tell, the choice is basically:
1) Don't have partitions and maintain Uniqueness of the ID's
2) Have partitions, but risk losing the uniqueness of the ID's


This doesn't seem like a very good answer, so was thinking I must be missing something?

Options: ReplyQuote


Subject
Views
Written By
Posted
Partitioning by date, and maintaining uniqueness
3782
August 13, 2013 09:51AM


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.