= 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)

}}}