CODEWITHSUNDEEP
mysqldatabase-partitioning
Adam
Adam

Reputation: 1971

Convert to a Partitioned Table

I have the following table structure with live data in it:

 CREATE TABLE IF NOT EXISTS `userstatistics` (
   `user_id` int(10) unsigned NOT NULL,
   `number_logons` int(7) unsigned NOT NULL DEFAULT '0',
   `number_profileminiviews` int(7) unsigned NOT NULL DEFAULT '0',
   `number_profilefullviews` int(7) unsigned NOT NULL DEFAULT '0',
   `number_mailsreceived` int(7) unsigned NOT NULL DEFAULT '0',
   `number_interestreceived` int(7) unsigned NOT NULL DEFAULT '0',
   `number_favouratesreceived` int(7) unsigned NOT NULL DEFAULT '0',
   `number_friendshiprequestreceived` int(7) unsigned NOT NULL DEFAULT '0',
   `number_imchatrequestreceived` int(7) unsigned NOT NULL DEFAULT '0',
   `yearweek` int(6) unsigned NOT NULL DEFAULT '0',
   PRIMARY KEY (`user_id`,`yearweek`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I want to convert this to a partitioned table with the following structure:

 CREATE TABLE IF NOT EXISTS `userstatistics` (
   `user_id` int(10) unsigned NOT NULL,
   `number_logons` int(7) unsigned NOT NULL DEFAULT '0',
   `number_profileminiviews` int(7) unsigned NOT NULL DEFAULT '0',
   `number_profilefullviews` int(7) unsigned NOT NULL DEFAULT '0',
   `number_mailsreceived` int(7) unsigned NOT NULL DEFAULT '0',
   `number_interestreceived` int(7) unsigned NOT NULL DEFAULT '0',
   `number_favouratesreceived` int(7) unsigned NOT NULL DEFAULT '0',
   `number_friendshiprequestreceived` int(7) unsigned NOT NULL DEFAULT '0',
   `number_imchatrequestreceived` int(7) unsigned NOT NULL DEFAULT '0',
   `yearweek` int(6) unsigned NOT NULL DEFAULT '0',
   PRIMARY KEY (`user_id`,`yearweek`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 /*!50100 PARTITION BY RANGE (yearweek)
 (PARTITION userstats_201108 VALUES LESS THAN (201108) ENGINE = InnoDB,
  PARTITION userstats_201109 VALUES LESS THAN (201109) ENGINE = InnoDB,
  PARTITION userstats_201110 VALUES LESS THAN (201110) ENGINE = InnoDB,
  PARTITION userstats_201111 VALUES LESS THAN (201111) ENGINE = InnoDB,
  PARTITION userstats_201112 VALUES LESS THAN (201112) ENGINE = InnoDB,
  PARTITION userstats_201113 VALUES LESS THAN (201113) ENGINE = InnoDB,
  PARTITION userstats_201114 VALUES LESS THAN (201114) ENGINE = InnoDB,
  PARTITION userstats_201115 VALUES LESS THAN (201115) ENGINE = InnoDB,
  PARTITION userstats_201116 VALUES LESS THAN (201116) ENGINE = InnoDB,
  PARTITION userstats_201117 VALUES LESS THAN (201117) ENGINE = InnoDB,
  PARTITION userstats_201118 VALUES LESS THAN (201118) ENGINE = InnoDB,
  PARTITION userstats_201119 VALUES LESS THAN (201119) ENGINE = InnoDB,
  PARTITION userstats_201120 VALUES LESS THAN (201120) ENGINE = InnoDB,
  PARTITION userstats_201121 VALUES LESS THAN (201121) ENGINE = InnoDB,
  PARTITION userstats_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;

How can I do this conversion?

Simply changing the first line of the second SQL statement to 

 ALTER TABLE 'userstatistics' (

Would this do it?

Going from MySQL 5.0 to 5.1.

Upvotes: 7

Views: 7733

Answers (2)

Clark Bao
Clark Bao

Reputation: 1693

Look this http://dev.mysql.com/doc/refman/5.1/en/alter-table.html about the alter table.

Then in particular the alter table.

ADD/DROP/COALESCE/REORGANIZE partition sql provides almost all the functions to manage your partitions.

note that hash can be only used to integer.

ALTER TABLE ... ADD PARTITION creates no temporary table except when used with NDB tables. ADD or DROP operations for RANGE or LIST partitions are immediate operations or nearly so. ADD or COALESCE operations for HASH or KEY partitions copy data between changed partitions; unless LINEAR HASH or LINEAR KEY was used, this is much the same as creating a new table (although the operation is done partition by partition). REORGANIZE operations copy only changed partitions and do not touch unchanged ones.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562320

First, you need to be running MySQL 5.1 or later. MySQL 5.0 does not support partitioning.

Second, please be aware of the difference between single-quotes (which delimit strings and dates) and back-ticks (which delimit table and column identifiers in MySQL). Use the correct type where appropriate. I mention this, because your example uses the wrong type of quotes:

ALTER TABLE 'userstatistics' (

That should be:

ALTER TABLE `userstatistics` (

Finally, yes, you can restructure a table into partitions with ALTER TABLE. Here's an exact copy & paste from a statement I tested on MySQL 5.1.57:

ALTER TABLE userstatistics PARTITION BY RANGE (yearweek)  (
PARTITION userstats_201108 VALUES LESS THAN (201108) ENGINE = InnoDB,   
PARTITION userstats_201109 VALUES LESS THAN (201109) ENGINE = InnoDB,   
PARTITION userstats_201110 VALUES LESS THAN (201110) ENGINE = InnoDB,   
PARTITION userstats_201111 VALUES LESS THAN (201111) ENGINE = InnoDB,   
PARTITION userstats_201112 VALUES LESS THAN (201112) ENGINE = InnoDB,   
PARTITION userstats_201113 VALUES LESS THAN (201113) ENGINE = InnoDB,   
PARTITION userstats_201114 VALUES LESS THAN (201114) ENGINE = InnoDB,   
PARTITION userstats_201115 VALUES LESS THAN (201115) ENGINE = InnoDB,   
PARTITION userstats_201116 VALUES LESS THAN (201116) ENGINE = InnoDB,   
PARTITION userstats_201117 VALUES LESS THAN (201117) ENGINE = InnoDB,   
PARTITION userstats_201118 VALUES LESS THAN (201118) ENGINE = InnoDB,   
PARTITION userstats_201119 VALUES LESS THAN (201119) ENGINE = InnoDB,   
PARTITION userstats_201120 VALUES LESS THAN (201120) ENGINE = InnoDB,   
PARTITION userstats_201121 VALUES LESS THAN (201121) ENGINE = InnoDB, 
PARTITION userstats_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

Note that this causes a table restructure, so if you already have a lot of data in this table, it will take a while to run. Exactly how long depends on how much data you have, and your hardware speed, and other factors. Be aware that while the table is being restructured, it is locked and unavailable for reading and writing by other queries.

Upvotes: 8

Related Questions