One common question I get is how to use partitioning instead of MERGE tables. The process I use involves using stored procedures to create and drop partitions. This article will go over the stored procedures I use; special thanks to Roland Bouman for taking a look and giving great feedback to optimize this process.
First, a simple table, not partitioned (yet):
use test; DROP TABLE IF EXISTS my_part; CREATE TABLE IF NOT EXISTS my_part ( id int NOT NULL, creationDate datetime NOT NULL, PRIMARY KEY (id,creationDate) ) ENGINE=InnoDB;
In real, life there is more to the table than just
creationDate. The most important part is that the partitioned field(s) need to be part of the primary key.
Now, add the partition definition. This can be done in the[Read more...]