1

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.

2 Answers 2

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 !!!

2
  • Thanks a lot but the problem is, this partition for this year if i want it in dynamic i.e for next I need to again follow same procedure is it?
    – Sush
    May 6, 2015 at 3:10
  • To make the RENAMEs atomic, do RENAME TABLE paramlog_ems TO paramlog_ems_old, paramlog_ems_new TO paramlog_ems; Then follow with DROP TABLE paramlog_ems_old; when you are comfortable that the conversion was ok.
    – Rick James
    May 6, 2015 at 4:55
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.