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.
Best Answer
Here are the Steps to Partition Your Table by Month
Create a Temp Table, Partitioned and Indexed on LogDate
Load the Temp Table
Swap the Temp Table
GIVE IT A TRY !!!