Convert to a Partitioned Table

Question

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.

Answer 1

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.