So this is just a simple example of how to set up a PARTITION and a SUBPARTITION in MySQL. The concept here is that you have data in a table with numerous values in a datetime field. You might have data that is spread across numerous years  (most likely you do). So one way to partition this data is to sort it by year but then also sort it by month within that yearly partition.

Below is an example that you can use for consideration.

Consider the test table. Your table with have many more fields of course.

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_time` datetime DEFAULT NOW(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;


First I will populate the test table with random values for the  date_time field.

delimiter //
CREATE PROCEDURE populate_t1( IN rowsofdata INT )
…

At Kscope this year, I attended a half day in-depth session entitled Data Warehousing Performance Best Practices, given by Maria Colgan of Oracle. My impression, which was confirmed by folks in the Oracle world, is that she knows her way around the Oracle optimizer.

See part 1 for the introduction and talking about power and hardware. This part will go over the 2nd “P”, partitioning. Learning about Oracle’s partitioning has gotten me more interested in how MySQL’s partitioning works, and I do hope that MySQL partitioning will develop to the level that Oracle partitioning does, because Oracle’s partitioning looks very nice (then again, that’s why it costs so much I guess).

Partition – …