= Partition =

1. mysql restore sample data (mysqlsampledatabase.sql) from [https://www.mysqltutorial.org/mysql-sample-database.aspx 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 [https://mysql.dbgeekgirl.com/2018/12/exchange-partition-archiving-strategy.html 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 payments3 exchange partition p2004 with table payments_2004;
}}}