Changes between Initial Version and Version 1 of Partition

Timestamp:

06/15/22 00:47:57 (2 years ago)

Author:

krit

Comment:

--

Partition

@@ +1 @@
+= 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 ) ) 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
+}}}
+
+3. 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)
+
+}}}