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. In this example, the primary key consists of two fields but the partition is defined on a single column. The partitioning rules require that the partition field should be part of the primary key, so whatever number of fields compose the primary key, a partition must employ at least one.

   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;
   

Test Script

Following is test script to parse to mysql command

CREATE DATABASE Payment;
USE Payment;
CREATE TABLE payments (
     customerNumber int(11) NOT NULL,
     checkNumber varchar(50) NOT NULL,
     paymentDate datetime NOT NULL,
     amount decimal(10,2) NOT NULL  )
     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')),
                   PARTITION p3 VALUES LESS THAN MAXVALUE );

SHOW CREATE TABLE payments \G
INSERT INTO payments select * from classicmodels.payments;

EXPLAIN SELECT * FROM payments where paymentDate = "2005-05-03";
EXPLAIN SELECT * FROM classicmodels.payments where paymentDate = "2005-05-03";
CREATE TABLE payments_2004 like payments;
SHOW CREATE TABLE payments_2004 \G
ALTER TABLE payments_2004 REMOVE PARTITIONING;
SHOW CREATE TABLE payments_2004 \G
ALTER TABLE payments EXCHANGE PARTITION p2004 WITH TABLE payments_2004;
SELECT COUNT(*)  FROM  payments;
SELECT COUNT(*)  FROM  payments_2004;

ALTER TABLE payments EXCHANGE PARTITION p2004 WITH TABLE payments_2004;
SELECT COUNT(*)  FROM  payments;
SELECT COUNT(*)  FROM  payments_2004;

-------------- query with parition ------------
SELECT * FROM table1 PARTITION (A, B) WHERE col1< 10

SELECT * FROM payments PARTITION (p2005) where paymentDate = "2005-05-03";
EXPLAIN SELECT * FROM payments PARTITION (p2005) WHERE paymentDate = "2005-05-03";
EXPLAIN SELECT * FROM payments WHERE customerNumber = 209;
EXPLAIN SELECT * FROM payments PARTITION (p2005) WHERE customerNumber = 209;

------------ with primary key -----------------
EXPLAIN SELECT * FROM classicmodels.payments WHERE customerNumber = 209;
EXPLAIN SELECT * FROM classicmodels.payments WHERE amount = 4632.31;
EXPLAIN SELECT * FROM payments WHERE amount = 4632.31;
EXPLAIN SELECT * FROM payments PARTITION (p2004)  WHERE amount = 4632.31;

Clear Disk space

Ref ​here