Changes between Version 9 and Version 10 of Partition

Timestamp:

06/16/22 07:33:59 (3 years ago)

Author:

krit

Comment:

--

Partition

@@ -74 +74 @@
 }}}
 
+== 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;
+
+
+}}}