How to define partition according to Date?

Question

I am currently using MySQL 5.6.

In my database, I have a log table called paramlog_ems. It has 2M rows

I want like to partition this log table according to Date.

Here is the table structure:

CREATE TABLE paramlog_ems (
  SiteIndex smallint(5) unsigned NOT NULL,
  RegionIndex smallint(5) unsigned NOT NULL,
  OrganizationIndex smallint(5) unsigned NOT NULL,
  DeviceSlaveId smallint(5) unsigned NOT NULL,
  UserIndex smallint(5) unsigned NOT NULL,
  ParameterID smallint(5) unsigned NOT NULL,
  ParamValue double(20,10) NOT NULL,
  DeviceName varchar(50) NOT NULL,
  LogDate datetime NOT NULL,
  Category tinyint(3) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I want to do this using MySQL Workbench.

Answer 1

Here are the Steps to Partition Your Table by Month

Create a Temp Table, Partitioned and Indexed on LogDate

CREATE TABLE paramlog_ems_new (
  SiteIndex smallint(5) unsigned NOT NULL,
  RegionIndex smallint(5) unsigned NOT NULL,
  OrganizationIndex smallint(5) unsigned NOT NULL,
  DeviceSlaveId smallint(5) unsigned NOT NULL,
  UserIndex smallint(5) unsigned NOT NULL,
  ParameterID smallint(5) unsigned NOT NULL,
  ParamValue double(20,10) NOT NULL,
  DeviceName varchar(50) NOT NULL,
  LogDate datetime NOT NULL,
  Category tinyint(3) NOT NULL,
  KEY LogDate (LogDate)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY RANGE COLUMNS(`LogDate`)
(PARTITION p00000000000 VALUES LESS THAN ('2015-01-01') ENGINE = MyISAM,
 PARTITION p2015_01_jan VALUES LESS THAN ('2015-02-01') ENGINE = MyISAM,
 PARTITION p2015_02_feb VALUES LESS THAN ('2015-03-01') ENGINE = MyISAM,
 PARTITION p2015_03_mar VALUES LESS THAN ('2015-04-01') ENGINE = MyISAM,
 PARTITION p2015_04_apr VALUES LESS THAN ('2015-05-01') ENGINE = MyISAM,
 PARTITION p2015_05_may VALUES LESS THAN ('2015-06-01') ENGINE = MyISAM,
 PARTITION p2015_06_jun VALUES LESS THAN ('2015-07-01') ENGINE = MyISAM,
 PARTITION p2015_07_jul VALUES LESS THAN ('2015-08-01') ENGINE = MyISAM,
 PARTITION p2015_08_aug VALUES LESS THAN ('2015-09-01') ENGINE = MyISAM,
 PARTITION p2015_09_sep VALUES LESS THAN ('2015-10-01') ENGINE = MyISAM,
 PARTITION p2015_10_oct VALUES LESS THAN ('2015-11-01') ENGINE = MyISAM,
 PARTITION p2015_11_nov VALUES LESS THAN ('2015-12-01') ENGINE = MyISAM,
 PARTITION p2015_12_dec VALUES LESS THAN ('2016-01-01') ENGINE = MyISAM,
 PARTITION p99999999999 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM);

Load the Temp Table

INSERT INTO paramlog_ems_new SELECT * FROM paramlog_ems;

Swap the Temp Table

ALTER TABLE paramlog_ems RENAME paramlog_ems_old;
ALTER TABLE paramlog_ems_new RENAME paramlog_ems;

GIVE IT A TRY !!!

Answer 2

In addition to Rolando's suggested schema and my improvement on RENAME, you may want two more things:

1. Purge "old" data via DROP PARTITION. This is about the only real use for moving to partitioning.

2. Add a new partition using

   ALTER TABLE REORGANIZE PARTITION p99999999999 INTO
PARTITION p2016_01_jan VALUES LESS THAN ('2016-02-01'),
PARTITION p99999999999 VALUES LESS THAN (MAXVALUE);

More details and discussion in my Partition Maintenance blog, including a 'reference' Stored Procedure to do the monthly task.