Partition

1. mysql restore sample data (mysqlsampledatabase.sql) from ​here

   rpl_user@node1:/mysql/test/T3$ /mysql/bin/mysql -uroot --protocol=socket --socket=/tmp/mysql.0.sock <  mysqlsampledatabase.sql
   

2. create table payments2. primary key MUST include in PARTITION

   mysql> CREATE TABLE payments2 ( 
             customerNumber int(11) NOT NULL, 
             checkNumber varchar(50) NOT NULL, 
             paymentDate datetime NOT NULL, 
             amount decimal(10,2) NOT NULL, 
             PRIMARY KEY (customerNumber,paymentDate ) ) 
             ENGINE=InnoDB PARTITION BY RANGE( TO_DAYS(paymentDate) ) 
             ( PARTITION p2003 VALUES LESS THAN (TO_DAYS('2004-01-01')), 
               PARTITION p2004 VALUES LESS THAN (TO_DAYS('2005-01-01')), 
               PARTITION p2005 VALUES LESS THAN (TO_DAYS('2006-01-01')) );
   

3. copy payments table to just create payments2

   mysql> insert into payments2 select * from payments
   

4. test and notice the rows between table payments (273) vs payments2 (37)

   mysql> explain select * from payments2 where paymentDate = "2005-05-03";
   +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
   | id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
   +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
   |  1 | SIMPLE      | payments3 | p2005      | ALL  | NULL          | NULL | NULL    | NULL |   37 |    10.00 | Using where |
   +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
   1 row in set, 1 warning (0.00 sec)
   
   mysql> explain select * from payments where paymentDate = "2005-05-03";
   +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
   | id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
   +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
   |  1 | SIMPLE      | payments | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  273 |    10.00 | Using where |
   +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
   1 row in set, 1 warning (0.00 sec)
   
   mysql> SELECT COUNT(*) FROM payments2 PARTITION (p2004);
   +----------+
   | COUNT(*) |
   +----------+
   |      136 |
   +----------+
   1 row in set (0.00 sec)
   
   

5. Archive partition base on ​here. First, we create a table like payments2

   mysql> create table payments_2004 like payments2;
   mysql> show create table payments_2004 \G
   

6. We remove partition from new table that we create

   mysql> alter table payments_2004 remove partitioning;
   

7. We move partition of payments2 to payments_2004

   mysql> alter table payments2 exchange partition p2004 with table payments_2004;
   

8. To move back from payments_2004 to partition p2004 in payment2

   mysql> alter table payments2 exchange partition p2004 with table payments_2004;