# Lab 2: Partitioned Tables ### Overview + In these exercises, you will create and manage Range partitioning, Range COLUMNS partitioning, List partitioning and List COLUMNS partitioning tables. You will learn about the differences between Hash partitioning and Key partitioning tables. Finally, you will compare the performance between partitioned and non-partitioned table. ### Notes + Click [pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon) to download the private key file, which is required to log into the Control Machine for this lab. + Commands and SQL statements presented on this page are clickable for easy copy and paste. + Your environment may be different from the one shown on the lab guide, including but not limited to IP, port, username, password and directory. Example output is displayed in a gray background in this guide. + The `$` in your terminal window is an OS prompt. The `tidb>` or `mysql>` in your terminal window is SQL command prompt. ### Assumptions + The private key file is downloaded and placed under `~/.ssh/` folder locally. Relevant permission has been provided by [chmod 400 ~/.ssh/pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon) + For Windows users, please refer to this [guide](https://github.com/pingcap-inc/tidb-course-labs/blob/master/README_303_class_setup_guide_for_student_lab_control_en.md) for the private key preparation. ## Exercise 1 ### Subject: RANGE, RANGE COLUMN, and RANGE INTERVAL partitioned tables ### Overview + In this exercise, you will create and manage Range partitioning, Range COLUMNS partitioning and Range INTERVAL partitioning table. ### Duration + This exercise should take you approximately 10 minutes to complete. ### Tasks + Connect to TiDB and create a new non-partitioned table `emp` and insert 36 rows into the `emp` table. + Create a new Range partitioning table `employees1` without primary key. + Create a new COLUMNS Range partitioning table `employees2` without primary key. + Create a new Range partitioning table `employees3` whit primary key. + Create a new COLUMNS Range partitioning table `employees4` without primary key. + Import the data from the non-partitioned table `emp` into the partitioned tables `employees1`, `employees2`, `employees3`, and `employees4`, and check the partition information. + Use the `reorganize partition` command to merge two partitions from `employees1`, `employees2`, `employees3`, and `employees4` into one partition, and view the results after merging. + Use the `DROP PARTITION` command to delete unnecessary partitions from `employees1`, `employees2`, `employees3`, and `employees4`. + Create two new Range INTERVAL partitioning: table `employees5` with Range partitioning and table `employees6` with Range COLUMNS partitioning, and display their partition information. ### Solution 0. Login as user ec2-user on monitor1_address. If you do not possess the credentials to connect, kindly review the Assumptions section under the OVERVIEW tab. The returned message may be slightly different from what is displayed here. If prompted, answer `yes`. ```command $ ssh -i ~/.ssh/pe-class-key-oregon.pem ec2-user@monitor1_address ``` ```output , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` 1. Connect to TiDB, and create a new non-partitioned table `emp` and insert 36 rows into the `emp` table. ```command $ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```command use test; ``` ```output Database changed ``` ```command CREATE TABLE emp ( id INT PRIMARY KEY, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '2018-01-01', dept_code INT NOT NULL, region_id INT NOT NULL ); INSERT INTO emp VALUES(1, 'Chars', 'Smith', '2018-05-08', 1, 801); INSERT INTO emp VALUES(2, 'Mott', 'Johnson', '2018-06-01', 2, 401); INSERT INTO emp VALUES(3, 'Lundeen', 'Williams', '2018-06-22', 3, 201); INSERT INTO emp VALUES(4, 'Broe', 'Brown', '2018-09-22', 4, 101); INSERT INTO emp VALUES(5, 'Layla', 'Jones', '2018-10-12', 5, 301); INSERT INTO emp VALUES(6, 'Tom', 'Garcia', '2019-01-30', 2, 501); INSERT INTO emp VALUES(7, 'Donald', 'Miller', '2019-02-21', 2, 401); INSERT INTO emp VALUES(8, 'Kevin', 'Davis', '2019-03-09', 3, 301); INSERT INTO emp VALUES(9, 'Ken', 'Rodriguez', '2020-11-17', 1, 201); INSERT INTO emp VALUES(10, 'Bill', 'Martinez', '2020-11-19', 2, 201); INSERT INTO emp VALUES(11, 'Jack', 'Hernandez', '2020-12-01', 4, 201); INSERT INTO emp VALUES(12, 'Fran', 'Jones', '2020-12-01', 4, 201); INSERT INTO emp VALUES(13, 'Mark', 'Wilson', '2021-07-08', 4, 101); INSERT INTO emp VALUES(14, 'Bob', 'Thomas', '2021-07-08', 4, 101); INSERT INTO emp VALUES(15, 'Jeff', 'Taylor', '2022-04-03', 5, 101); INSERT INTO emp VALUES(16, 'Jim', 'Lee', '2022-04-04', 2, 201); INSERT INTO emp VALUES(17, 'Tim', 'Anderson', '2022-05-10', 2, 301); INSERT INTO emp VALUES(18, 'Mike', 'Jackson', '2022-05-10', 3, 401); INSERT INTO emp VALUES(19, 'Benjamin', 'Thompson', '2022-09-24', 1, 501); INSERT INTO emp VALUES(20, 'Charlotte', 'Clark', '2022-09-24', 1, 801); INSERT INTO emp VALUES(21, 'Mason', 'Jones', '2022-10-27', 1, 801); INSERT INTO emp VALUES(22, 'Emma', 'Robinson', '2023-06-15', 2, 301); INSERT INTO emp VALUES(23, 'Ava', 'Adams', '2023-07-03', 2, 301); INSERT INTO emp VALUES(24, 'Olivia', 'Lewis', '2023-08-01', 5, 701); INSERT INTO emp VALUES(25, 'Ava', 'Phillips', '2023-09-02', 3, 701); INSERT INTO emp VALUES(26, 'Isabella', 'Morgan', '2023-09-03', 2, 101); INSERT INTO emp VALUES(27, 'Donald', 'Edwards', '2023-09-03', 4, 101); INSERT INTO emp VALUES(28, 'Somes', 'Alexander', '2023-12-10', 1, 101); INSERT INTO emp VALUES(29, 'Crabb', 'Ford', '2024-06-30', 2, 401); INSERT INTO emp VALUES(30, 'Sisika', 'Williams', '2024-06-30', 2, 401); INSERT INTO emp VALUES(31, 'Noah', 'Anderson', '2024-07-02', 4, 101); INSERT INTO emp VALUES(32, 'Jacob', 'Martinez', '2025-01-11', 4, 501); INSERT INTO emp VALUES(33, 'Kim', 'Miller', '2025-01-30', 1, 401); INSERT INTO emp VALUES(34, 'Jim', 'Smith', '2025-04-24', 2, 501); INSERT INTO emp VALUES(35, 'Sam', 'Martinez', '2025-05-22', 3, 101); INSERT INTO emp VALUES(36, 'Candy', 'Morgan', '2025-08-03', 1, 301); SELECT COUNT(*) from emp; ``` ```output tidb>CREATE TABLE emp ( -> id INT PRIMARY KEY, -> fname VARCHAR(30), -> lname VARCHAR(30), -> hired DATE NOT NULL DEFAULT '2018-01-01', -> dept_code INT NOT NULL, -> region_id INT NOT NULL -> ); Query OK, 0 rows affected (0.13 sec) tidb>INSERT INTO emp VALUES(1, 'Chars', 'Smith', '2018-05-08', 1, 801); Query OK, 1 row affected (0.01 sec) ... ... tidb>INSERT INTO emp VALUES(36, 'Candy', 'Morgan', '2025-08-03', 1, 301); Query OK, 1 row affected (0.01 sec) tidb>SELECT COUNT(*) FROM emp; +----------+ | COUNT(*) | +----------+ | 36 | +----------+ 1 row in set (0.00 sec) ``` 2. Create a new Range partitioning table `employees1` without primary key. ```command CREATE TABLE employees1 ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '2018-01-01', dept_code INT NOT NULL, region_id INT NOT NULL ) PARTITION BY RANGE (hired) ( PARTITION p0 VALUES LESS THAN ('2019-01-01'), PARTITION p1 VALUES LESS THAN ('2020-01-01'), PARTITION p2 VALUES LESS THAN ('2021-01-01'), PARTITION p3 VALUES LESS THAN ('2022-01-01'), PARTITION p4 VALUES LESS THAN ('2023-01-01'), PARTITION p5 VALUES LESS THAN ('2024-01-01'), PARTITION p6 VALUES LESS THAN ('2025-01-01'), PARTITION p7 VALUES LESS THAN ('2026-01-01'), PARTITION p8 VALUES LESS THAN MAXVALUE ); ``` ```output mysql> CREATE TABLE employees1 ( -> id INT NOT NULL, -> fname VARCHAR(30), -> lname VARCHAR(30), -> hired DATE NOT NULL DEFAULT '2018-01-01', -> dept_code INT NOT NULL, -> region_id INT NOT NULL -> ) -> PARTITION BY RANGE (hired) ( -> PARTITION p0 VALUES LESS THAN ('2019-01-01'), -> PARTITION p1 VALUES LESS THAN ('2020-01-01'), -> PARTITION p2 VALUES LESS THAN ('2021-01-01'), -> PARTITION p3 VALUES LESS THAN ('2022-01-01'), -> PARTITION p4 VALUES LESS THAN ('2023-01-01'), -> PARTITION p5 VALUES LESS THAN ('2024-01-01'), -> PARTITION p6 VALUES LESS THAN ('2025-01-01'), -> PARTITION p7 VALUES LESS THAN ('2026-01-01'), -> PARTITION p8 VALUES LESS THAN MAXVALUE -> ); ERROR 1697 (HY000): VALUES value for partition 'p0' must have type INT ``` + The reason for the error `ERROR 1697 (HY000): VALUES value for partition 'p0' must have type INT` is that the KEY after `PARTITION BY RANGE` must be of `INT` type. We need to modify the SQL statement accordingly. ```command CREATE TABLE employees1 ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '2018-01-01', dept_code INT NOT NULL, region_id INT NOT NULL ) PARTITION BY RANGE (YEAR(hired)) ( PARTITION p0 VALUES LESS THAN (2019), PARTITION p1 VALUES LESS THAN (2020), PARTITION p2 VALUES LESS THAN (2021), PARTITION p3 VALUES LESS THAN (2022), PARTITION p4 VALUES LESS THAN (2023), PARTITION p5 VALUES LESS THAN (2024), PARTITION p6 VALUES LESS THAN (2025), PARTITION p7 VALUES LESS THAN (2026), PARTITION p8 VALUES LESS THAN MAXVALUE ); ``` ```output tidb>CREATE TABLE employees1 ( -> id INT NOT NULL, -> fname VARCHAR(30), -> lname VARCHAR(30), -> hired DATE NOT NULL DEFAULT '2018-01-01', -> dept_code INT NOT NULL, -> region_id INT NOT NULL -> ) -> PARTITION BY RANGE (YEAR(hired)) ( -> PARTITION p0 VALUES LESS THAN (2019), -> PARTITION p1 VALUES LESS THAN (2020), -> PARTITION p2 VALUES LESS THAN (2021), -> PARTITION p3 VALUES LESS THAN (2022), -> PARTITION p4 VALUES LESS THAN (2023), -> PARTITION p5 VALUES LESS THAN (2024), -> PARTITION p6 VALUES LESS THAN (2025), -> PARTITION p7 VALUES LESS THAN (2026), -> PARTITION p8 VALUES LESS THAN MAXVALUE -> ); Query OK, 0 rows affected (0.13 sec) ``` 3. Create a new COLUMNS Range partitioning table `employees2` without primary key. ```command CREATE TABLE employees2 ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '2018-01-01', dept_code INT NOT NULL, region_id INT NOT NULL ) PARTITION BY RANGE COLUMNS(hired) ( PARTITION p0 VALUES LESS THAN ('2019-01-01'), PARTITION p1 VALUES LESS THAN ('2020-01-01'), PARTITION p2 VALUES LESS THAN ('2021-01-01'), PARTITION p3 VALUES LESS THAN ('2022-01-01'), PARTITION p4 VALUES LESS THAN ('2023-01-01'), PARTITION p5 VALUES LESS THAN ('2024-01-01'), PARTITION p6 VALUES LESS THAN ('2025-01-01'), PARTITION p7 VALUES LESS THAN ('2026-01-01'), PARTITION p8 VALUES LESS THAN MAXVALUE ); ``` ```output tidb>CREATE TABLE employees2 ( -> id INT NOT NULL, -> fname VARCHAR(30), -> lname VARCHAR(30), -> hired DATE NOT NULL DEFAULT '2018-01-01', -> dept_code INT NOT NULL, -> region_id INT NOT NULL -> ) -> PARTITION BY RANGE COLUMNS(hired) ( -> PARTITION p0 VALUES LESS THAN ('2019-01-01'), -> PARTITION p1 VALUES LESS THAN ('2020-01-01'), -> PARTITION p2 VALUES LESS THAN ('2021-01-01'), -> PARTITION p3 VALUES LESS THAN ('2022-01-01'), -> PARTITION p4 VALUES LESS THAN ('2023-01-01'), -> PARTITION p5 VALUES LESS THAN ('2024-01-01'), -> PARTITION p6 VALUES LESS THAN ('2025-01-01'), -> PARTITION p7 VALUES LESS THAN ('2026-01-01'), -> PARTITION p8 VALUES LESS THAN MAXVALUE -> ); Query OK, 0 rows affected (0.13 sec) ``` + The data types of partition columns can be integer, string (CHAR or VARCHAR), DATE, and DATETIME. Expressions, such as `YEAR()`, are not supported. You can use one or more columns as partitioning keys. 4. Create a new Range partitioning table `employees3` with primary key. ```command CREATE TABLE employees3 ( id INT PRIMARY KEY, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '2018-01-01', dept_code INT NOT NULL, region_id INT NOT NULL ) PARTITION BY RANGE (YEAR(hired)) ( PARTITION p0 VALUES LESS THAN (2019), PARTITION p1 VALUES LESS THAN (2020), PARTITION p2 VALUES LESS THAN (2021), PARTITION p3 VALUES LESS THAN (2022), PARTITION p4 VALUES LESS THAN (2023), PARTITION p5 VALUES LESS THAN (2024), PARTITION p6 VALUES LESS THAN (2025), PARTITION p7 VALUES LESS THAN (2026), PARTITION p8 VALUES LESS THAN MAXVALUE ); ``` ```output mysql> CREATE TABLE employees3 ( -> id INT PRIMARY KEY, -> fname VARCHAR(30), -> lname VARCHAR(30), -> hired DATE NOT NULL DEFAULT '2018-01-01', -> dept_code INT NOT NULL, -> region_id INT NOT NULL -> ) -> PARTITION BY RANGE (YEAR(hired)) ( -> PARTITION p0 VALUES LESS THAN (2019), -> PARTITION p1 VALUES LESS THAN (2020), -> PARTITION p2 VALUES LESS THAN (2021), -> PARTITION p3 VALUES LESS THAN (2022), -> PARTITION p4 VALUES LESS THAN (2023), -> PARTITION p5 VALUES LESS THAN (2024), -> PARTITION p6 VALUES LESS THAN (2025), -> PARTITION p7 VALUES LESS THAN (2026), -> PARTITION p8 VALUES LESS THAN MAXVALUE -> ); ERROR 1503 (HY000): A CLUSTERED INDEX must include all columns in the table's partitioning function ``` + The reason for the error `ERROR 1503 (HY000): A CLUSTERED INDEX must include all columns in the table's partitioning function` is that, for tables with unique keys, all columns in the partition expression must be included in the unique key. Primary key is also a unique key. ```command CREATE TABLE employees3 ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '2018-01-01', dept_code INT NOT NULL, region_id INT NOT NULL, PRIMARY KEY idx_id_hired(id, hired) ) PARTITION BY RANGE (YEAR(hired)) ( PARTITION p0 VALUES LESS THAN (2019), PARTITION p1 VALUES LESS THAN (2020), PARTITION p2 VALUES LESS THAN (2021), PARTITION p3 VALUES LESS THAN (2022), PARTITION p4 VALUES LESS THAN (2023), PARTITION p5 VALUES LESS THAN (2024), PARTITION p6 VALUES LESS THAN (2025), PARTITION p7 VALUES LESS THAN (2026), PARTITION p8 VALUES LESS THAN MAXVALUE ); ``` ```output tidb>CREATE TABLE employees3 ( -> id INT NOT NULL, -> fname VARCHAR(30), -> lname VARCHAR(30), -> hired DATE NOT NULL DEFAULT '2018-01-01', -> dept_code INT NOT NULL, -> region_id INT NOT NULL, -> PRIMARY KEY idx_id_hired(id, hired) -> ) -> PARTITION BY RANGE (YEAR(hired)) ( -> PARTITION p0 VALUES LESS THAN (2019), -> PARTITION p1 VALUES LESS THAN (2020), -> PARTITION p2 VALUES LESS THAN (2021), -> PARTITION p3 VALUES LESS THAN (2022), -> PARTITION p4 VALUES LESS THAN (2023), -> PARTITION p5 VALUES LESS THAN (2024), -> PARTITION p6 VALUES LESS THAN (2025), -> PARTITION p7 VALUES LESS THAN (2026), -> PARTITION p8 VALUES LESS THAN MAXVALUE -> ); Query OK, 0 rows affected (0.13 sec) ``` 5. Create a new COLUMNS Range partitioning table `employees4` with primary key. ```command CREATE TABLE employees4 ( id INT PRIMARY KEY, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '2018-01-01', dept_code INT NOT NULL, region_id INT NOT NULL ) PARTITION BY RANGE COLUMNS(hired) ( PARTITION p0 VALUES LESS THAN ('2019-01-01'), PARTITION p1 VALUES LESS THAN ('2020-01-01'), PARTITION p2 VALUES LESS THAN ('2021-01-01'), PARTITION p3 VALUES LESS THAN ('2022-01-01'), PARTITION p4 VALUES LESS THAN ('2023-01-01'), PARTITION p5 VALUES LESS THAN ('2024-01-01'), PARTITION p6 VALUES LESS THAN ('2025-01-01'), PARTITION p7 VALUES LESS THAN ('2026-01-01'), PARTITION p8 VALUES LESS THAN MAXVALUE ); ``` ```output tidb>CREATE TABLE employees4 ( -> id INT PRIMARY KEY, -> fname VARCHAR(30), -> lname VARCHAR(30), -> hired DATE NOT NULL DEFAULT '2018-01-01', -> dept_code INT NOT NULL, -> region_id INT NOT NULL -> ) -> PARTITION BY RANGE COLUMNS(hired) ( -> PARTITION p0 VALUES LESS THAN ('2019-01-01'), -> PARTITION p1 VALUES LESS THAN ('2020-01-01'), -> PARTITION p2 VALUES LESS THAN ('2021-01-01'), -> PARTITION p3 VALUES LESS THAN ('2022-01-01'), -> PARTITION p4 VALUES LESS THAN ('2023-01-01'), -> PARTITION p5 VALUES LESS THAN ('2024-01-01'), -> PARTITION p6 VALUES LESS THAN ('2025-01-01'), -> PARTITION p7 VALUES LESS THAN ('2026-01-01'), -> PARTITION p8 VALUES LESS THAN MAXVALUE -> ); ERROR 1503 (HY000): A CLUSTERED INDEX must include all columns in the table's partitioning function ``` + The reason for the error `ERROR 1503 (HY000): A CLUSTERED INDEX must include all columns in the table's partitioning function` is that, for tables with unique keys, all columns in the partition expression must be included in the unique key. Primary key is also a unique key. ```command CREATE TABLE employees4 ( id INT, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '2018-01-01', dept_code INT NOT NULL, region_id INT NOT NULL, PRIMARY KEY idx_id_hired(id, hired) ) PARTITION BY RANGE COLUMNS(hired) ( PARTITION p0 VALUES LESS THAN ('2019-01-01'), PARTITION p1 VALUES LESS THAN ('2020-01-01'), PARTITION p2 VALUES LESS THAN ('2021-01-01'), PARTITION p3 VALUES LESS THAN ('2022-01-01'), PARTITION p4 VALUES LESS THAN ('2023-01-01'), PARTITION p5 VALUES LESS THAN ('2024-01-01'), PARTITION p6 VALUES LESS THAN ('2025-01-01'), PARTITION p7 VALUES LESS THAN ('2026-01-01'), PARTITION p8 VALUES LESS THAN MAXVALUE ); ``` ```output tidb>CREATE TABLE employees4 ( -> id INT, -> fname VARCHAR(30), -> lname VARCHAR(30), -> hired DATE NOT NULL DEFAULT '2018-01-01', -> dept_code INT NOT NULL, -> region_id INT NOT NULL, -> PRIMARY KEY idx_id_hired(id, hired) -> ) -> PARTITION BY RANGE COLUMNS(hired) ( -> PARTITION p0 VALUES LESS THAN ('2019-01-01'), -> PARTITION p1 VALUES LESS THAN ('2020-01-01'), -> PARTITION p2 VALUES LESS THAN ('2021-01-01'), -> PARTITION p3 VALUES LESS THAN ('2022-01-01'), -> PARTITION p4 VALUES LESS THAN ('2023-01-01'), -> PARTITION p5 VALUES LESS THAN ('2024-01-01'), -> PARTITION p6 VALUES LESS THAN ('2025-01-01'), -> PARTITION p7 VALUES LESS THAN ('2026-01-01'), -> PARTITION p8 VALUES LESS THAN MAXVALUE -> ); Query OK, 0 rows affected (0.14 sec) ``` 6. Import the data from the non-partitioned table `emp` into the partitioned tables `employees1`, `employees2`, `employees3`, and `employees4`, and check the partition information. Then, Use the `reorganize partition` command to merge two partitions from `employees1`, `employees2`, `employees3`, and `employees4` into one partition, and view the results after merging. ```command INSERT INTO employees1 SELECT * FROM emp; INSERT INTO employees2 SELECT * FROM emp; INSERT INTO employees3 SELECT * FROM emp; INSERT INTO employees4 SELECT * FROM emp; ANALYZE TABLE employees1; ANALYZE TABLE employees2; ANALYZE TABLE employees3; ANALYZE TABLE employees4; SELECT table_name, partition_name, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name IN ('employees1', 'employees2', 'employees3', 'employees4') ORDER BY table_name, partition_name; ``` ```output tidb>INSERT INTO employees1 SELECT * FROM emp; Query OK, 36 rows affected (0.02 sec) Records: 36 Duplicates: 0 Warnings: 0 tidb>INSERT INTO employees2 SELECT * FROM emp; Query OK, 36 rows affected (0.01 sec) Records: 36 Duplicates: 0 Warnings: 0 tidb>INSERT INTO employees3 SELECT * FROM emp; Query OK, 36 rows affected (0.01 sec) Records: 36 Duplicates: 0 Warnings: 0 tidb>INSERT INTO employees4 SELECT * FROM emp; Query OK, 36 rows affected (0.01 sec) Records: 36 Duplicates: 0 Warnings: 0 tidb>ANALYZE TABLE employees1; Query OK, 0 rows affected, 9 warnings (1.27 sec) tidb>ANALYZE TABLE employees2; Query OK, 0 rows affected, 18 warnings (1.07 sec) tidb>ANALYZE TABLE employees3; Query OK, 0 rows affected, 18 warnings (1.27 sec) tidb>ANALYZE TABLE employees4; Query OK, 0 rows affected, 18 warnings (1.31 sec) tidb>SELECT table_name, partition_name, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name IN ('employees1', 'employees2', 'employees3', 'employees4') ORDER BY table_name, partition_name; +------------+----------------+------------+ | table_name | partition_name | table_rows | +------------+----------------+------------+ | employees1 | p0 | 5 | | employees1 | p1 | 3 | | employees1 | p2 | 4 | | employees1 | p3 | 2 | | employees1 | p4 | 7 | | employees1 | p5 | 7 | | employees1 | p6 | 3 | | employees1 | p7 | 5 | | employees1 | p8 | 0 | | employees2 | p0 | 5 | | employees2 | p1 | 3 | | employees2 | p2 | 4 | | employees2 | p3 | 2 | | employees2 | p4 | 7 | | employees2 | p5 | 7 | | employees2 | p6 | 3 | | employees2 | p7 | 5 | | employees2 | p8 | 0 | | employees3 | p0 | 5 | | employees3 | p1 | 3 | | employees3 | p2 | 4 | | employees3 | p3 | 2 | | employees3 | p4 | 7 | | employees3 | p5 | 7 | | employees3 | p6 | 3 | | employees3 | p7 | 5 | | employees3 | p8 | 0 | | employees4 | p0 | 5 | | employees4 | p1 | 3 | | employees4 | p2 | 4 | | employees4 | p3 | 2 | | employees4 | p4 | 7 | | employees4 | p5 | 7 | | employees4 | p6 | 3 | | employees4 | p7 | 5 | | employees4 | p8 | 0 | +------------+----------------+------------+ 36 rows in set (0.01 sec) ``` + You might see warning message code `1105` after running the `ANALYZE TABLE` command. The warning message states that the `dynamic pruning mode` is disabled, since there is no global statistics available. You can check [this documentation](https://docs.pingcap.com/tidb/v7.5/partitioned-table#dynamic-pruning-mode) for details. ```command ALTER TABLE employees1 REORGANIZE PARTITION p6,p7 into (PARTITION pErr VALUES LESS THAN (2026)); ALTER TABLE employees2 REORGANIZE PARTITION p6,p7 into (PARTITION pErr VALUES LESS THAN ('2026-01-01')); ALTER TABLE employees3 REORGANIZE PARTITION p6,p7 into (PARTITION pErr VALUES LESS THAN (2026)); ALTER TABLE employees4 REORGANIZE PARTITION p5,p7 into (PARTITION pErr VALUES LESS THAN ('2026-01-01')); ALTER TABLE employees4 REORGANIZE PARTITION p6,p7 into (PARTITION pErr VALUES LESS THAN ('2026-01-01')); ANALYZE TABLE employees1; ANALYZE TABLE employees2; ANALYZE TABLE employees3; ANALYZE TABLE employees4; SELECT table_name, partition_name, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name IN ('employees1', 'employees2', 'employees3', 'employees4') ORDER BY table_name, partition_name; ``` ```output tidb>ALTER TABLE employees1 REORGANIZE PARTITION p6,p7 into (PARTITION pErr VALUES LESS THAN (2026)); Query OK, 0 rows affected, 1 warning (0.95 sec) tidb>ALTER TABLE employees2 REORGANIZE PARTITION p6,p7 into (PARTITION pErr VALUES LESS THAN ('2026-01-01')); Query OK, 0 rows affected, 1 warning (0.95 sec) tidb>ALTER TABLE employees3 REORGANIZE PARTITION p6,p7 into (PARTITION pErr VALUES LESS THAN (2026)); Query OK, 0 rows affected, 1 warning (0.86 sec) tidb>ALTER TABLE employees4 REORGANIZE PARTITION p5,p7 into (PARTITION pErr VALUES LESS THAN ('2026-01-01')); ERROR 8200 (HY000): Unsupported REORGANIZE PARTITION of RANGE; not adjacent partitions tidb>ALTER TABLE employees4 REORGANIZE PARTITION p6,p7 into (PARTITION pErr VALUES LESS THAN ('2026-01-01')); Query OK, 0 rows affected, 1 warning (0.96 sec) tidb>ANALYZE TABLE employees1; Query OK, 0 rows affected, 9 warnings (1.27 sec) tidb>ANALYZE TABLE employees2; Query OK, 0 rows affected, 18 warnings (1.07 sec) tidb>ANALYZE TABLE employees3; Query OK, 0 rows affected, 18 warnings (1.27 sec) tidb>ANALYZE TABLE employees4; Query OK, 0 rows affected, 18 warnings (1.31 sec) tidb>SELECT table_name, partition_name, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name IN ('employees1', 'employees2', 'employees3', 'employees4') ORDER BY table_name, partition_name; +------------+----------------+------------+ | table_name | partition_name | table_rows | +------------+----------------+------------+ | employees1 | p0 | 5 | | employees1 | p1 | 3 | | employees1 | p2 | 4 | | employees1 | p3 | 2 | | employees1 | p4 | 7 | | employees1 | p5 | 7 | | employees1 | p8 | 0 | | employees1 | pErr | 8 | | employees2 | p0 | 5 | | employees2 | p1 | 3 | | employees2 | p2 | 4 | | employees2 | p3 | 2 | | employees2 | p4 | 7 | | employees2 | p5 | 7 | | employees2 | p8 | 0 | | employees2 | pErr | 8 | | employees3 | p0 | 5 | | employees3 | p1 | 3 | | employees3 | p2 | 4 | | employees3 | p3 | 2 | | employees3 | p4 | 7 | | employees3 | p5 | 7 | | employees3 | p8 | 0 | | employees3 | pErr | 8 | | employees4 | p0 | 5 | | employees4 | p1 | 3 | | employees4 | p2 | 4 | | employees4 | p3 | 2 | | employees4 | p4 | 7 | | employees4 | p5 | 7 | | employees4 | p8 | 0 | | employees4 | pErr | 8 | +------------+----------------+------------+ 32 rows in set (0.01 sec) ``` + The reason for the error `ERROR 8200 (HY000): Unsupported REORGANIZE PARTITION of RANGE; not adjacent partitions` when creating the `employees4` table is that for a Range partition table, you can only merge adjacent partitions. 7. Use the `DROP PARTITION` command to delete unnecessary partitions `pErr` from `employees1`, `employees2`, `employees3`, and `employees4`. ```command ALTER TABLE employees1 DROP PARTITION pErr; ALTER TABLE employees2 DROP PARTITION pErr; ALTER TABLE employees3 DROP PARTITION pErr; ALTER TABLE employees4 DROP PARTITION pErr; ANALYZE TABLE employees1; ANALYZE TABLE employees2; ANALYZE TABLE employees3; ANALYZE TABLE employees4; SELECT table_name, partition_name, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name IN ('employees1', 'employees2', 'employees3', 'employees4') ORDER BY table_name, partition_name; ``` ```output tidb>ALTER TABLE employees1 DROP PARTITION pErr; Query OK, 0 rows affected (0.28 sec) tidb>ALTER TABLE employees2 DROP PARTITION pErr; Query OK, 0 rows affected (0.32 sec) tidb>ALTER TABLE employees3 DROP PARTITION pErr; Query OK, 0 rows affected (0.29 sec) tidb>ALTER TABLE employees4 DROP PARTITION pErr; Query OK, 0 rows affected (0.29 sec) tidb>ANALYZE TABLE employees1; Query OK, 0 rows affected, 9 warnings (1.27 sec) tidb>ANALYZE TABLE employees2; Query OK, 0 rows affected, 18 warnings (1.07 sec) tidb>ANALYZE TABLE employees3; Query OK, 0 rows affected, 18 warnings (1.27 sec) tidb>ANALYZE TABLE employees4; Query OK, 0 rows affected, 18 warnings (1.31 sec) tidb>SELECT table_name, partition_name, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name IN ('employees1', 'employees2', 'employees3', 'employees4') ORDER BY table_name, partition_name; +------------+----------------+------------+ | table_name | partition_name | table_rows | +------------+----------------+------------+ | employees1 | p0 | 5 | | employees1 | p1 | 3 | | employees1 | p2 | 4 | | employees1 | p3 | 2 | | employees1 | p4 | 7 | | employees1 | p5 | 7 | | employees1 | p8 | 0 | | employees2 | p0 | 5 | | employees2 | p1 | 3 | | employees2 | p2 | 4 | | employees2 | p3 | 2 | | employees2 | p4 | 7 | | employees2 | p5 | 7 | | employees2 | p8 | 0 | | employees3 | p0 | 5 | | employees3 | p1 | 3 | | employees3 | p2 | 4 | | employees3 | p3 | 2 | | employees3 | p4 | 7 | | employees3 | p5 | 7 | | employees3 | p8 | 0 | | employees4 | p0 | 5 | | employees4 | p1 | 3 | | employees4 | p2 | 4 | | employees4 | p3 | 2 | | employees4 | p4 | 7 | | employees4 | p5 | 7 | | employees4 | p8 | 0 | +------------+----------------+------------+ 28 rows in set (0.01 sec) ``` 8. Create two new Range INTERVAL partitioning table `employees5` with Range partitioning and table `employees6` with Range COLUMNS partitioning, and retrieving partition information of them. ```command CREATE TABLE employees5 ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '2018-01-01', dept_code INT NOT NULL, region_id INT NOT NULL ) PARTITION BY RANGE (YEAR(hired)) INTERVAL (1) FIRST PARTITION LESS THAN (2019) LAST PARTITION LESS THAN (2026) MAXVALUE PARTITION; CREATE TABLE employees6 ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '2018-01-01', dept_code INT NOT NULL, region_id INT NOT NULL ) PARTITION BY RANGE COLUMNS (hired) INTERVAL (1 MONTH) FIRST PARTITION LESS THAN ('2019-01-01') LAST PARTITION LESS THAN ('2026-01-01') MAXVALUE PARTITION; INSERT INTO employees5 SELECT * FROM emp; INSERT INTO employees6 SELECT * FROM emp; ANALYZE TABLE employees5; ANALYZE TABLE employees6; SELECT table_name, partition_name, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name IN ('employees5', 'employees6') ORDER BY table_name, partition_name; ``` ```output tidb>CREATE TABLE employees5 ( -> id INT NOT NULL, -> fname VARCHAR(30), -> lname VARCHAR(30), -> hired DATE NOT NULL DEFAULT '2018-01-01', -> dept_code INT NOT NULL, -> region_id INT NOT NULL -> ) -> PARTITION BY RANGE (YEAR(hired)) -> INTERVAL (1) FIRST PARTITION LESS THAN (2019) LAST PARTITION LESS THAN (2026) MAXVALUE PARTITION; Query OK, 0 rows affected (0.13 sec) tidb>CREATE TABLE employees6 ( -> id INT NOT NULL, -> fname VARCHAR(30), -> lname VARCHAR(30), -> hired DATE NOT NULL DEFAULT '2018-01-01', -> dept_code INT NOT NULL, -> region_id INT NOT NULL -> ) -> PARTITION BY RANGE COLUMNS (hired) -> INTERVAL (1 MONTH) FIRST PARTITION LESS THAN ('2019-01-01') LAST PARTITION LESS THAN ('2026-01-01') MAXVALUE PARTITION; Query OK, 0 rows affected (0.15 sec) tidb>INSERT INTO employees5 SELECT * FROM emp; Query OK, 36 rows affected (0.02 sec) Records: 36 Duplicates: 0 Warnings: 0 tidb>INSERT INTO employees6 SELECT * FROM emp; Query OK, 36 rows affected (0.02 sec) Records: 36 Duplicates: 0 Warnings: 0 tidb>ANALYZE TABLE employees5; Query OK, 0 rows affected, 18 warnings (1.27 sec) tidb>ANALYZE TABLE employees6; Query OK, 0 rows affected, 18 warnings (1.31 sec) tidb>SELECT table_name, partition_name, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name IN ('employees5', 'employees6') ORDER BY table_name, partition_name; +------------+-----------------+------------+ | table_name | partition_name | table_rows | +------------+-----------------+------------+ | employees5 | P_LT_2019 | 5 | | employees5 | P_LT_2020 | 3 | | employees5 | P_LT_2021 | 4 | | employees5 | P_LT_2022 | 2 | | employees5 | P_LT_2023 | 7 | | employees5 | P_LT_2024 | 7 | | employees5 | P_LT_2025 | 3 | | employees5 | P_LT_2026 | 5 | | employees5 | P_MAXVALUE | 0 | | employees6 | P_LT_2019-01-01 | 5 | | employees6 | P_LT_2019-02-01 | 1 | | employees6 | P_LT_2019-03-01 | 1 | | employees6 | P_LT_2019-04-01 | 1 | | employees6 | P_LT_2019-05-01 | 0 | | employees6 | P_LT_2019-06-01 | 0 | | employees6 | P_LT_2019-07-01 | 0 | | employees6 | P_LT_2019-08-01 | 0 | | employees6 | P_LT_2019-09-01 | 0 | | employees6 | P_LT_2019-10-01 | 0 | | employees6 | P_LT_2019-11-01 | 0 | | employees6 | P_LT_2019-12-01 | 0 | | employees6 | P_LT_2020-01-01 | 0 | | employees6 | P_LT_2020-02-01 | 0 | | employees6 | P_LT_2020-03-01 | 0 | | employees6 | P_LT_2020-04-01 | 0 | | employees6 | P_LT_2020-05-01 | 0 | | employees6 | P_LT_2020-06-01 | 0 | | employees6 | P_LT_2020-07-01 | 0 | | employees6 | P_LT_2020-08-01 | 0 | | employees6 | P_LT_2020-09-01 | 0 | | employees6 | P_LT_2020-10-01 | 0 | | employees6 | P_LT_2020-11-01 | 0 | | employees6 | P_LT_2020-12-01 | 2 | | employees6 | P_LT_2021-01-01 | 2 | | employees6 | P_LT_2021-02-01 | 0 | | employees6 | P_LT_2021-03-01 | 0 | | employees6 | P_LT_2021-04-01 | 0 | | employees6 | P_LT_2021-05-01 | 0 | | employees6 | P_LT_2021-06-01 | 0 | | employees6 | P_LT_2021-07-01 | 0 | | employees6 | P_LT_2021-08-01 | 2 | | employees6 | P_LT_2021-09-01 | 0 | | employees6 | P_LT_2021-10-01 | 0 | | employees6 | P_LT_2021-11-01 | 0 | | employees6 | P_LT_2021-12-01 | 0 | | employees6 | P_LT_2022-01-01 | 0 | | employees6 | P_LT_2022-02-01 | 0 | | employees6 | P_LT_2022-03-01 | 0 | | employees6 | P_LT_2022-04-01 | 0 | | employees6 | P_LT_2022-05-01 | 2 | | employees6 | P_LT_2022-06-01 | 2 | | employees6 | P_LT_2022-07-01 | 0 | | employees6 | P_LT_2022-08-01 | 0 | | employees6 | P_LT_2022-09-01 | 0 | | employees6 | P_LT_2022-10-01 | 2 | | employees6 | P_LT_2022-11-01 | 1 | | employees6 | P_LT_2022-12-01 | 0 | | employees6 | P_LT_2023-01-01 | 0 | | employees6 | P_LT_2023-02-01 | 0 | | employees6 | P_LT_2023-03-01 | 0 | | employees6 | P_LT_2023-04-01 | 0 | | employees6 | P_LT_2023-05-01 | 0 | | employees6 | P_LT_2023-06-01 | 0 | | employees6 | P_LT_2023-07-01 | 1 | | employees6 | P_LT_2023-08-01 | 1 | | employees6 | P_LT_2023-09-01 | 1 | | employees6 | P_LT_2023-10-01 | 3 | | employees6 | P_LT_2023-11-01 | 0 | | employees6 | P_LT_2023-12-01 | 0 | | employees6 | P_LT_2024-01-01 | 1 | | employees6 | P_LT_2024-02-01 | 0 | | employees6 | P_LT_2024-03-01 | 0 | | employees6 | P_LT_2024-04-01 | 0 | | employees6 | P_LT_2024-05-01 | 0 | | employees6 | P_LT_2024-06-01 | 0 | | employees6 | P_LT_2024-07-01 | 2 | | employees6 | P_LT_2024-08-01 | 1 | | employees6 | P_LT_2024-09-01 | 0 | | employees6 | P_LT_2024-10-01 | 0 | | employees6 | P_LT_2024-11-01 | 0 | | employees6 | P_LT_2024-12-01 | 0 | | employees6 | P_LT_2025-01-01 | 0 | | employees6 | P_LT_2025-02-01 | 2 | | employees6 | P_LT_2025-03-01 | 0 | | employees6 | P_LT_2025-04-01 | 0 | | employees6 | P_LT_2025-05-01 | 1 | | employees6 | P_LT_2025-06-01 | 1 | | employees6 | P_LT_2025-07-01 | 0 | | employees6 | P_LT_2025-08-01 | 0 | | employees6 | P_LT_2025-09-01 | 1 | | employees6 | P_LT_2025-10-01 | 0 | | employees6 | P_LT_2025-11-01 | 0 | | employees6 | P_LT_2025-12-01 | 0 | | employees6 | P_LT_2026-01-01 | 0 | | employees6 | P_MAXVALUE | 0 | +------------+-----------------+------------+ 95 rows in set (0.01 sec) ``` + You see that Range INTERVAL partitioning supports two methods: PARTITION BY RANGE and PARTITION BY RANGE COLUMNS. It can also automatically partition according to the interval set. 9. Clean up the tables. ```command DROP TABLE emp; DROP TABLE employees1; DROP TABLE employees2; DROP TABLE employees3; DROP TABLE employees4; DROP TABLE employees5; DROP TABLE employees6; ``` ```output tidb>DROP TABLE emp; Query OK, 0 rows affected (0.29 sec) tidb>DROP TABLE employees1; Query OK, 0 rows affected (0.34 sec) tidb>DROP TABLE employees2; Query OK, 0 rows affected (0.33 sec) tidb>DROP TABLE employees3; Query OK, 0 rows affected (0.33 sec) tidb>DROP TABLE employees4; Query OK, 0 rows affected (0.33 sec) tidb>DROP TABLE employees5; Query OK, 0 rows affected (0.34 sec) tidb>DROP TABLE employees6; Query OK, 0 rows affected (0.81 sec) ``` ## Exercise 2 ### Subject: EXCHANGE PARTITION ### Overview + In this exercise, we will create a List COLUMNS partitioning table, insert data into the table, and then use EXCHANGE PARTITION to exchange the data between the partitions and a non-partitioned table. ### Duration + This exercise should take you approximately 5 minutes to complete. ### Tasks + Create a non-partitioned table `students`. + Check the variables `tidb_enable_list_partition` for creating a new LIST COLUMNS partitioning table. + Create a new LIST COLUMNS partitioning table named `students1`. + Retrieving partition information of table `students1` and insert 3 rows. + Exchange one of the table `students1`'s partitions to the on-partitioned table `students` and view the result. ### Solution 1. Create a non-partitioned table `students`. ```command CREATE TABLE students ( id INT PRIMARY KEY, fname VARCHAR(30), lname VARCHAR(30), city VARCHAR(30), DOB DATE NOT NULL, dept INT NOT NULL ); ``` ```output tidb>CREATE TABLE students ( -> id INT PRIMARY KEY, -> fname VARCHAR(30), -> lname VARCHAR(30), -> city VARCHAR(30), -> DOB DATE NOT NULL, -> dept INT NOT NULL -> ); Query OK, 0 rows affected (0.53 sec) ``` 2. Check the variables `tidb_enable_list_partition` for creating a new LIST COLUMNS partitioning table. ```command SHOW VARIABLES LIKE 'tidb_enable_list_partition'; ``` ```output tidb>SHOW VARIABLES LIKE 'tidb_enable_list_partition'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | tidb_enable_list_partition | ON | +----------------------------+-------+ 1 row in set (0.00 sec) ``` + Before creating a List partitioned table, you need to set the value of the session variable tidb_enable_list_partition to ON. 3. Create a new LIST COLUMNS partitioning table named `students1`. ```command CREATE TABLE students1 ( id INT PRIMARY KEY, fname VARCHAR(30), lname VARCHAR(30), city VARCHAR(30), DOB DATE NOT NULL, dept INT NOT NULL ) PARTITION BY LIST ( city )( PARTITION p0 VALUES IN ('NewYork','BeiJing','Tokyo'), PARTITION p1 VALUES IN ('Atlanta', 'ShangHai', 'Sydney'), PARTITION p2 VALUES IN ('Boston', 'NanJing', 'Chicago')); ``` ```output tidb>CREATE TABLE students1 ( -> id INT PRIMARY KEY, -> fname VARCHAR(30), -> lname VARCHAR(30), -> city VARCHAR(30), -> DOB DATE NOT NULL, -> dept INT NOT NULL -> ) -> PARTITION BY LIST ( city )( -> PARTITION p0 VALUES IN ('NewYork','BeiJing','Tokyo'), -> PARTITION p1 VALUES IN ('Atlanta', 'ShangHai', 'Sydney'), -> PARTITION p2 VALUES IN ('Boston', 'NanJing', 'Chicago')); ERROR 1697 (HY000): VALUES value for partition 'p0' must have type INT ``` + The reason for the error `ERROR 1697 (HY000): VALUES value for partition 'p0' must have type INT` is that the KEY following PARTITION BY LIST must be of `INT` type. ```command CREATE TABLE students1 ( id INT PRIMARY KEY, fname VARCHAR(30), lname VARCHAR(30), city VARCHAR(30), DOB DATE NOT NULL, dept INT NOT NULL ) PARTITION BY LIST COLUMNS ( city )( PARTITION p0 VALUES IN ('NewYork','BeiJing','Tokyo'), PARTITION p1 VALUES IN ('Atlanta', 'ShangHai', 'Sydney'), PARTITION p2 VALUES IN ('Boston', 'NanJing', 'Chicago')); ``` ```output tidb>CREATE TABLE students1 ( -> id INT PRIMARY KEY, -> fname VARCHAR(30), -> lname VARCHAR(30), -> city VARCHAR(30), -> DOB DATE NOT NULL, -> dept INT NOT NULL -> ) -> PARTITION BY LIST COLUMNS ( city )( -> PARTITION p0 VALUES IN ('NewYork','BeiJing','Tokyo'), -> PARTITION p1 VALUES IN ('Atlanta', 'ShangHai', 'Sydney'), -> PARTITION p2 VALUES IN ('Boston', 'NanJing', 'Chicago')); ERROR 1503 (HY000): A CLUSTERED INDEX must include all columns in the table's partitioning function ``` + The reason for the error `ERROR 1503 (HY000): A CLUSTERED INDEX must include all columns in the table's partitioning function` is that, for tables with unique keys, all columns in the partition expression must be included in the unique key. Primary key is also a unique key. ```command CREATE TABLE students1 ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), city VARCHAR(30), DOB DATE NOT NULL, dept INT NOT NULL, PRIMARY KEY(id, city) ) PARTITION BY LIST COLUMNS ( city )( PARTITION p0 VALUES IN ('NewYork','BeiJing','Tokyo'), PARTITION p1 VALUES IN ('Atlanta', 'ShangHai', 'Sydney'), PARTITION p2 VALUES IN ('Boston', 'NanJing', 'Chicago')); ``` ```output tidb>CREATE TABLE students1 ( -> id INT NOT NULL, -> fname VARCHAR(30), -> lname VARCHAR(30), -> city VARCHAR(30), -> DOB DATE NOT NULL, -> dept INT NOT NULL, -> PRIMARY KEY(id, city) -> ) -> PARTITION BY LIST COLUMNS ( city )( -> PARTITION p0 VALUES IN ('NewYork','BeiJing','Tokyo'), -> PARTITION p1 VALUES IN ('Atlanta', 'ShangHai', 'Sydney'), -> PARTITION p2 VALUES IN ('Boston', 'NanJing', 'Chicago')); Query OK, 0 rows affected (0.54 sec) ``` 4. Insert some values into the table `students1`. ```command SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='students1' ORDER BY partition_name; INSERT INTO students1 VALUES(1, 'Chars', 'Smith', 'NewYork', '2005-01-02', 1); INSERT INTO students1 VALUES(2, 'Tom', 'Garcia', 'NewYork', '2005-02-01', 6); INSERT INTO students1 VALUES(3, 'Crabb', 'Ford', 'NewYork', '2004-11-19', 7); ANALYZE TABLE students1; SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='students1' ORDER BY partition_name; ``` ```output tidb>SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='students1' ORDER BY partition_name; +------------+----------------+-------------------------------+------------+ | table_name | partition_name | partition_description | table_rows | +------------+----------------+-------------------------------+------------+ | students1 | p0 | 'NewYork','BeiJing','Tokyo' | 0 | | students1 | p1 | 'Atlanta','ShangHai','Sydney' | 0 | | students1 | p2 | 'Boston','NanJing','Chicago' | 0 | +------------+----------------+-------------------------------+------------+ 3 rows in set (0.01 sec) tidb>INSERT INTO students1 VALUES(1, 'Chars', 'Smith', 'NewYork', '2005-01-02', 1); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO students1 VALUES(2, 'Tom', 'Garcia', 'NewYork', '2005-02-01', 6); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO students1 VALUES(3, 'Crabb', 'Ford', 'NewYork', '2004-11-19', 7); Query OK, 1 row affected (0.00 sec) tidb>ANALYZE TABLE students1; Query OK, 0 rows affected, 18 warnings (1.27 sec) tidb>SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='students1' ORDER BY partition_name; +------------+----------------+-------------------------------+------------+ | table_name | partition_name | partition_description | table_rows | +------------+----------------+-------------------------------+------------+ | students1 | p0 | 'NewYork','BeiJing','Tokyo' | 3 | | students1 | p1 | 'Atlanta','ShangHai','Sydney' | 0 | | students1 | p2 | 'Boston','NanJing','Chicago' | 0 | +------------+----------------+-------------------------------+------------+ 3 rows in set (0.00 sec) ``` + We can see that all the data is on the `p0` partition. 5. Exchange one of the table `students1`‘s partitions to the non-partitioned table `students` and view the result. ```command ALTER TABLE students1 EXCHANGE PARTITION `p0` WITH TABLE `students`; ``` ```output tidb>ALTER TABLE students1 EXCHANGE PARTITION `p0` WITH TABLE `students`; ERROR 1736 (HY000): Tables have different definitions ``` + The reason for the error `ERROR 1736 (HY000): Tables have different definitions` is that the primary key in table `students1` is column `(id, city)`, while in table `students` it is only column `id`. We will make adjustments below. ```command DROP TABLE students; CREATE TABLE students ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), city VARCHAR(30), DOB DATE NOT NULL, dept INT NOT NULL, PRIMARY KEY(id, city) ); ALTER TABLE students1 EXCHANGE PARTITION `p0` WITH TABLE `students`; ANALYZE TABLE students1; SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='students1' ORDER BY partition_name; SELECT * FROM students; ``` ```output tidb>DROP TABLE students; Query OK, 0 rows affected (0.52 sec) tidb>CREATE TABLE students ( -> id INT NOT NULL, -> fname VARCHAR(30), -> lname VARCHAR(30), -> city VARCHAR(30), -> DOB DATE NOT NULL, -> dept INT NOT NULL, -> PRIMARY KEY(id, city) -> ); Query OK, 0 rows affected (0.53 sec) tidb>ALTER TABLE students1 EXCHANGE PARTITION `p0` WITH TABLE `students`; Query OK, 0 rows affected, 1 warning (0.52 sec) tidb>ANALYZE TABLE students1; Query OK, 0 rows affected, 18 warnings (1.27 sec) tidb>SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='students1' ORDER BY partition_name; +------------+----------------+-------------------------------+------------+ | table_name | partition_name | partition_description | table_rows | +------------+----------------+-------------------------------+------------+ | students1 | p0 | 'NewYork','BeiJing','Tokyo' | 0 | | students1 | p1 | 'Atlanta','ShangHai','Sydney' | 0 | | students1 | p2 | 'Boston','NanJing','Chicago' | 0 | +------------+----------------+-------------------------------+------------+ 3 rows in set (0.00 sec) tidb>SELECT * FROM students; +----+-------+--------+---------+------------+------+ | id | fname | lname | city | DOB | dept | +----+-------+--------+---------+------------+------+ | 1 | Chars | Smith | NewYork | 2005-01-02 | 1 | | 2 | Tom | Garcia | NewYork | 2005-02-01 | 6 | | 3 | Crabb | Ford | NewYork | 2004-11-19 | 7 | +----+-------+--------+---------+------------+------+ 3 rows in set (0.00 sec) ``` 6. Clean up the tables. ```command DROP TABLE students; DROP TABLE students1; ``` ```output tidb>DROP TABLE students; Query OK, 0 rows affected (0.52 sec) tidb>DROP TABLE students1; Query OK, 0 rows affected (0.52 sec) ``` ## Exercise 3 ### Subject: Hash and Key partitioned tables ### Overview + In this exercise, we will create a Hash partitioning table and a Key partitioning table. After inserting the same data into both tables, we will compare the distribution of the data. ### Duration + This exercise should take you approximately 5 minutes to complete. ### Tasks + Create a new Hash partitioning table named `employees1`. + Insert rows into the table `employees1` and check the distribution of the Hash partitioning table. + Create a new Key partitioning table named `employees2`. + Insert rows into the table `employees2` and check the distribution of the Key partitioning table. ### Solution 1. Create a new Hash partitioning table named `employees1`. ```command CREATE TABLE employees1( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', job_code INT ) PARTITION BY HASH(YEAR(hired)) PARTITIONS 5; ``` ```output tidb>CREATE TABLE employees1( -> id INT NOT NULL, -> fname VARCHAR(30), -> lname VARCHAR(30), -> hired DATE NOT NULL DEFAULT '1970-01-01', -> job_code INT -> ) -> PARTITION BY HASH(YEAR(hired)) -> PARTITIONS 5; Query OK, 0 rows affected (0.54 sec) ``` 2. Insert rows into the table `employees1` and check the distribution of the Hash partitioning table. ```command INSERT INTO employees1 VALUES(1, 'Jim', 'Lee', '2023-04-04', 2); INSERT INTO employees1 VALUES(2, 'Tim', 'Anderson', '2023-05-10', 2); INSERT INTO employees1 VALUES(3, 'Mike', 'Jackson', '2023-05-10', 3); INSERT INTO employees1 VALUES(4, 'Benjamin', 'Thompson', '2023-09-24', 1); INSERT INTO employees1 VALUES(5, 'Charlotte', 'Clark', '2023-09-24', 1); ``` ```output tidb>INSERT INTO employees1 VALUES(1, 'Jim', 'Lee', '2023-04-04', 2); Query OK, 1 row affected (0.01 sec) ... ... tidb>INSERT INTO employees1 VALUES(5, 'Charlotte', 'Clark', '2023-09-24', 1); Query OK, 1 row affected (0.00 sec) ``` + We found that all rows inserted have same YEAR(hired) value. Let's check the distribution of the data. ```command ANALYZE TABLE employees1; SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='employees1' ORDER BY partition_name; ``` ```outputs tidb>ANALYZE TABLE employees1; Query OK, 0 rows affected, 10 warnings (0.60 sec) tidb>SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='employees1' ORDER BY partition_name; +------------+----------------+-----------------------+------------+ | table_name | partition_name | partition_description | table_rows | +------------+----------------+-----------------------+------------+ | employees1 | p0 | | 0 | | employees1 | p1 | | 0 | | employees1 | p2 | | 0 | | employees1 | p3 | | 5 | | employees1 | p4 | | 0 | +------------+----------------+-----------------------+------------+ 5 rows in set (0.01 sec) ``` + You can see that all rows are stored in partition `p3`. Why? Let's now truncate the table `employees1` and insert 5 new rows again. ```command TRUNCATE TABLE employees1; INSERT INTO employees1 VALUES(1, 'Jim', 'Lee', '2020-04-04', 2); INSERT INTO employees1 VALUES(2, 'Tim', 'Anderson', '2021-05-10', 2); INSERT INTO employees1 VALUES(3, 'Mike', 'Jackson', '2022-05-10', 3); INSERT INTO employees1 VALUES(4, 'Benjamin', 'Thompson', '2023-09-24', 1); INSERT INTO employees1 VALUES(5, 'Charlotte', 'Clark', '2024-09-24', 1); ``` ```output tidb>TRUNCATE TABLE employees1; Query OK, 0 rows affected (0.53 sec) ... ... tidb>INSERT INTO employees1 VALUES(5, 'Charlotte', 'Clark', '2024-09-24', 1); Query OK, 1 row affected (0.01 sec) ``` + This time all rows inserted have different `YEAR(hired)` value. Let's check the distribution of the data. ```command ANALYZE TABLE employees1; SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='employees1' ORDER BY partition_name; ``` ```output tidb>ANALYZE TABLE employees1; Query OK, 0 rows affected, 10 warnings (0.60 sec) tidb>SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='employees1' ORDER BY partition_name; +------------+----------------+-----------------------+------------+ | table_name | partition_name | partition_description | table_rows | +------------+----------------+-----------------------+------------+ | employees1 | p0 | | 1 | | employees1 | p1 | | 1 | | employees1 | p2 | | 1 | | employees1 | p3 | | 1 | | employees1 | p4 | | 1 | +------------+----------------+-----------------------+------------+ 5 rows in set (0.01 sec) ``` + This time there is one row each partition. 3. Create a new Key partitioning table named `employees2`. ```command CREATE TABLE employees2( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', job_code INT ) PARTITION BY KEY(hired) PARTITIONS 5; ``` ```output tidb>CREATE TABLE employees2( -> id INT NOT NULL, -> fname VARCHAR(30), -> lname VARCHAR(30), -> hired DATE NOT NULL DEFAULT '1970-01-01', -> job_code INT -> ) -> PARTITION BY KEY(hired) -> PARTITIONS 5; Query OK, 0 rows affected (0.54 sec) ``` 4. Insert rows into the table `employees2` and check the distribution of the Key partitioning table. ```command INSERT INTO employees2 VALUES(1, 'Jim', 'Lee', '2023-04-04', 2); INSERT INTO employees2 VALUES(2, 'Tim', 'Anderson', '2023-05-10', 2); INSERT INTO employees2 VALUES(3, 'Mike', 'Jackson', '2023-05-10', 3); INSERT INTO employees2 VALUES(4, 'Benjamin', 'Thompson', '2023-09-24', 1); INSERT INTO employees2 VALUES(5, 'Charlotte', 'Clark', '2023-09-24', 1); ``` ```output tidb>INSERT INTO employees2 VALUES(1, 'Jim', 'Lee', '2023-04-04', 2); Query OK, 1 row affected (0.02 sec) ... ... tidb>INSERT INTO employees2 VALUES(5, 'Charlotte', 'Clark', '2023-09-24', 1); Query OK, 1 row affected (0.00 sec) ``` + All rows inserted into `employees2` have different `hired` value, but the `VALUES` of `YEAR(hired)` are same. Let's check the distribution of the data. ```command ANALYZE TABLE employees2; SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='employees2' ORDER BY partition_name; ``` ```output tidb>ANALYZE TABLE employees2; Query OK, 0 rows affected, 10 warnings (0.64 sec) tidb>SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='employees2' ORDER BY partition_name; +------------+----------------+-----------------------+------------+ | table_name | partition_name | partition_description | table_rows | +------------+----------------+-----------------------+------------+ | employees2 | p0 | | 2 | | employees2 | p1 | | 2 | | employees2 | p2 | | 0 | | employees2 | p3 | | 1 | | employees2 | p4 | | 0 | +------------+----------------+-----------------------+------------+ 5 rows in set (0.02 sec) ``` + The rows are distribute in 3 partitions, even the values of YEAR(hired) are same. Let's truncate the table `employees2` and insert 5 new rows again. ```command TRUNCATE TABLE employees2; INSERT INTO employees2 VALUES(1, 'Jim', 'Lee', '2021-05-10', 2); INSERT INTO employees2 VALUES(2, 'Tim', 'Anderson', '2021-05-10', 2); INSERT INTO employees2 VALUES(3, 'Mike', 'Jackson', '2021-05-10', 3); INSERT INTO employees2 VALUES(4, 'Benjamin', 'Thompson', '2021-05-10', 1); INSERT INTO employees2 VALUES(5, 'Charlotte', 'Clark', '2021-05-10', 1); ``` ```output tidb>TRUNCATE TABLE employees2; Query OK, 0 rows affected (0.54 sec) tidb>INSERT INTO employees2 VALUES(1, 'Jim', 'Lee', '2021-05-10', 2); Query OK, 1 row affected (0.02 sec) ... ... tidb>INSERT INTO employees2 VALUES(5, 'Charlotte', 'Clark', '2021-05-10', 1); Query OK, 1 row affected (0.00 sec) ``` + All rows inserted into `employees2` have the same `hired` value. Let's check the distribution of the data. ```command ANALYZE TABLE employees2; SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='employees2' ORDER BY partition_name; ``` ```output tidb>ANALYZE TABLE employees2; Query OK, 0 rows affected, 10 warnings (0.60 sec) tidb>SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='employees2' ORDER BY partition_name; +------------+----------------+-----------------------+------------+ | table_name | partition_name | partition_description | table_rows | +------------+----------------+-----------------------+------------+ | employees2 | p0 | | 0 | | employees2 | p1 | | 0 | | employees2 | p2 | | 0 | | employees2 | p3 | | 0 | | employees2 | p4 | | 5 | +------------+----------------+-----------------------+------------+ 5 rows in set (0.01 sec) ``` + All rows are stored in partition `p4`. Why? Let's truncate the table `employees2` and insert 5 new rows again. ```command TRUNCATE TABLE employees2; INSERT INTO employees2 VALUES(1, 'Jim', 'Lee', '2020-04-04', 2); INSERT INTO employees2 VALUES(2, 'Tim', 'Anderson', '2021-05-10', 2); INSERT INTO employees2 VALUES(3, 'Mike', 'Jackson', '2022-05-10', 3); INSERT INTO employees2 VALUES(4, 'Benjamin', 'Thompson', '2023-09-24', 1); INSERT INTO employees2 VALUES(5, 'Charlotte', 'Clark', '2024-09-24', 1); ``` ```output tidb>INSERT INTO employees2 VALUES(1, 'Jim', 'Lee', '2020-04-04', 2); Query OK, 1 row affected (0.01 sec) ... ... tidb>INSERT INTO employees2 VALUES(5, 'Charlotte', 'Clark', '2024-09-24', 1); Query OK, 1 row affected (0.00 sec) ``` + All rows inserted into `employees2` have different `hired` value. Let's check the distribution of the data. ```command ANALYZE TABLE employees2; SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='employees2' ORDER BY partition_name; ``` ```output tidb>ANALYZE TABLE employees1; Query OK, 0 rows affected, 10 warnings (0.60 sec) tidb>SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='employees2' ORDER BY partition_name; +------------+----------------+-----------------------+------------+ | table_name | partition_name | partition_description | table_rows | +------------+----------------+-----------------------+------------+ | employees2 | p0 | | 1 | | employees2 | p1 | | 2 | | employees2 | p2 | | 1 | | employees2 | p3 | | 0 | | employees2 | p4 | | 1 | +------------+----------------+-----------------------+------------+ 5 rows in set (0.01 sec) ``` 5. Clean up the tables. ```command DROP TABLE employees1; DROP TABLE employees2; ``` ```output tidb>DROP TABLE employees1; Query OK, 0 rows affected (0.52 sec) tidb>DROP TABLE employees2; Query OK, 0 rows affected (0.53 sec) ``` ## Exercise 4 ### Subject: Performance comparison ### Overview + In this exercise, we evaluate the effect of partitioning tables on performance. + Partition an existing table by using LIST COLUMNS partitioning. + Execute queries against both the original and partitioned tables to compare performance. ### Duration + This exercise should take you approximately 15 minutes to complete. ### Tasks + Create a new non-partitioned table named `orders_no_partition` under test database, and insert some data into it. + Create a new LIST COLUMNS partitioning table named `orders_partition` under test database, and insert some data into it. + Compare the `CREATE TABLE` statements used to create the `orders_no_partition` and `orders_partition` tables. How is the `orders_partition` table partitioned? What other differences are there between the tables? + Query the `PARTITIONS` table in the `INFORMATION_SCHEMA` database to retrieve the number of rows and the data length for each partition in the `orders_partition` table. + Compare the query performance of the `orders_no_partition` and `orders_partition` tables by executing select query test against each of the tables, with the following SQL: + `SELECT * FROM test.orders_no_partition WHERE Type = 6;` + `SELECT * FROM test.orders_partition WHERE Type = 6;` ### Solution 1. Create a new non-partitioned table named `orders_no_partition` under test database, and insert some values into the table. ```command CREATE TABLE orders_no_partition( ID int(11) NOT NULL AUTO_INCREMENT, Name char(35) NOT NULL DEFAULT '', StoreCode char(3) NOT NULL DEFAULT '', City char(35) NOT NULL DEFAULT '', Mount int(11) NOT NULL DEFAULT '0', Type int(11) NOT NULL DEFAULT '0', PRIMARY KEY(ID), Key idx_StoreCode(StoreCode) ); ``` ```output tidb> CREATE TABLE orders_no_partition( -> ID int(11) NOT NULL AUTO_INCREMENT, -> Name char(35) NOT NULL DEFAULT '', -> StoreCode char(3) NOT NULL DEFAULT '', -> City char(35) NOT NULL DEFAULT '', -> Mount int(11) NOT NULL DEFAULT '0', -> Type int(11) NOT NULL DEFAULT '0', -> PRIMARY KEY(ID), -> Key idx_StoreCode(StoreCode) -> ); Query OK, 0 rows affected (0.14 sec) ``` ```command EXIT for i in `seq 1000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -e "INSERT INTO test.orders_no_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 1);"; done; for i in `seq 1000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -e "INSERT INTO test.orders_no_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 2);"; done; for i in `seq 1000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -e "INSERT INTO test.orders_no_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 3);"; done; for i in `seq 1000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -e "INSERT INTO test.orders_no_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 4);"; done; for i in `seq 1000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -e "INSERT INTO test.orders_no_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 5);"; done; for i in `seq 9`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -e "INSERT INTO test.orders_no_partition SELECT NULL, Name, StoreCode, City,Mount, Type FROM test.orders_no_partition;"; done; ``` ```output tidb>EXIT Bye $ for i in `seq 1000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -e "INSERT INTO test.orders_no_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 1);"; done; mysql: [Warning] Using a password on the command line interface can be insecure. ... ... mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. ``` 2. Create a new LIST COLUMNS partition table named `orders_partition` under test database, and insert some data into the table. ```command $ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```command use test; ``` ```output Database changed ``` ```command CREATE TABLE orders_partition( ID int(11) NOT NULL AUTO_INCREMENT, Name char(35) NOT NULL DEFAULT '', StoreCode char(3) NOT NULL DEFAULT '', City char(35) NOT NULL DEFAULT '', Mount int(11) NOT NULL DEFAULT '0', Type int(11) NOT NULL DEFAULT '0', PRIMARY KEY(ID, Type), Key idx_StoreCode(StoreCode) ) PARTITION BY LIST COLUMNS (Type)( PARTITION p0 VALUES IN (1), PARTITION p1 VALUES IN (2), PARTITION p2 VALUES IN (3), PARTITION p3 VALUES IN (4), PARTITION p4 VALUES IN (5) ); ``` ```output tidb> CREATE TABLE orders_partition( -> ID int(11) NOT NULL AUTO_INCREMENT, -> Name char(35) NOT NULL DEFAULT '', -> StoreCode char(3) NOT NULL DEFAULT '', -> City char(35) NOT NULL DEFAULT '', -> Mount int(11) NOT NULL DEFAULT '0', -> Type int(11) NOT NULL DEFAULT '0', -> PRIMARY KEY(ID, Type), -> Key idx_StoreCode(StoreCode) -> ) -> PARTITION BY LIST COLUMNS (Type)( -> PARTITION p0 VALUES IN (1), -> PARTITION p1 VALUES IN (2), -> PARTITION p2 VALUES IN (3), -> PARTITION p3 VALUES IN (4), -> PARTITION p4 VALUES IN (5) -> ); Query OK, 0 rows affected (0.15 sec) ``` ```command EXIT for i in `seq 1000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -e "INSERT INTO test.orders_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 1);"; done; for i in `seq 1000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -e "INSERT INTO test.orders_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 2);"; done; for i in `seq 1000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -e "INSERT INTO test.orders_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 3);"; done; for i in `seq 1000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -e "INSERT INTO test.orders_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 4);"; done; for i in `seq 1000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -e "INSERT INTO test.orders_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 5);"; done; for i in `seq 9`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -e "INSERT INTO test.orders_partition SELECT NULL, Name, StoreCode, City,Mount, Type from test.orders_partition;"; done; ``` ```output tidb>EXIT Bye $ for i in `seq 1000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -e "INSERT INTO test.orders_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 1);"; done; mysql: [Warning] Using a password on the command line interface can be insecure. ... ... mysql: [Warning] Using a password on the command line interface can be insecure. ``` 3. Connect to TiDB and insert two rows into each of the `orders_no_partition` and `orders_partition` tables. ```command $ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```command use test; ``` ```output Database changed ``` ```command INSERT INTO test.orders_no_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 6); INSERT INTO test.orders_no_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 6); INSERT INTO test.orders_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 6); ``` ```output tidb>INSERT INTO test.orders_no_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 6); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO test.orders_no_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 6); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO test.orders_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 6); ERROR 1526 (HY000): Table has no partition for value from column_list ``` + The reason for the error `ERROR 1526 (HY000): Table has no partition for value from column_list` is that the table `orders_partition` does not have a partition for `Type 6`, so a partition needs to be added. ```command ALTER TABLE test.orders_partition ADD PARTITION (PARTITION p5 VALUES IN (6)); INSERT INTO test.orders_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 6); INSERT INTO test.orders_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 6); ``` ```output tidb>ALTER TABLE test.orders_partition ADD PARTITION (PARTITION p5 VALUES IN (6)); Query OK, 0 rows affected (0.22 sec) tidb>INSERT INTO test.orders_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 6); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO test.orders_partition VALUES(NULL,SUBSTRING(MD5(RAND()),1,20), SUBSTRING(MD5(RAND()),1,3), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10000), 6); Query OK, 1 row affected (0.00 sec) ``` 4. Execute SHOW `TABLE STATUS` on the `orders_no_partition` table. What value does the `Create_options` column in the output contain? ```command SHOW TABLE STATUS LIKE 'orders_no_partition'\G ``` ```output tidb>SHOW TABLE STATUS LIKE 'orders_no_partition'\G *************************** 1. row *************************** Name: orders_no_partition Engine: InnoDB Version: 10 Row_format: Compact Rows: 2560000 Avg_row_length: 70 Data_length: 179200000 Max_data_length: 0 Index_length: 10240000 Data_free: 0 Auto_increment: 2560002 Create_time: 2023-11-16 15:10:53 Update_time: NULL Check_time: NULL Collation: utf8mb4_bin Checksum: Create_options: Comment: 1 row in set (0.01 sec) ``` + Answer: The `Create_options` column in the `SHOW TABLE STATUS` output for the `orders_no_partition` table is empty. 5. Execute SHOW `TABLE STATUS` on the `orders_partition` table. What value does the `Create_options` column in the output contain? ```command SHOW TABLE STATUS LIKE 'orders_partition'\G ``` ```output tidb>SHOW TABLE STATUS LIKE 'orders_partition'\G *************************** 1. row *************************** Name: orders_partition Engine: InnoDB Version: 10 Row_format: Compact Rows: 2560002 Avg_row_length: 70 Data_length: 179200140 Max_data_length: 0 Index_length: 51200040 Data_free: 0 Auto_increment: 2560004 Create_time: 2023-11-16 15:48:21 Update_time: NULL Check_time: NULL Collation: utf8mb4_bin Checksum: Create_options: partitioned Comment: 1 row in set (0.01 sec) ``` + Answer: The `Create_options` column in the `SHOW TABLE STATUS` output for the `orders_partition` table is partitioned. 6. Compare the `CREATE TABLE` statements used to create the `orders_no_partition` and `orders_partition` tables. How is the `orders_partition` table partitioned? ```command SHOW CREATE TABLE orders_no_partition\G SHOW CREATE TABLE orders_partition\G ``` ```output tidb>SHOW CREATE TABLE orders_no_partition\G *************************** 1. row *************************** Table: orders_no_partition Create Table: CREATE TABLE `orders_no_partition` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `Name` char(35) NOT NULL DEFAULT '', `StoreCode` char(3) NOT NULL DEFAULT '', `City` char(35) NOT NULL DEFAULT '', `Mount` int(11) NOT NULL DEFAULT '0', `Type` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`) /*T![clustered_index] CLUSTERED */, KEY `idx_StoreCode` (`StoreCode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=3182134 1 row in set (0.01 sec) tidb>SHOW CREATE TABLE orders_partition\G *************************** 1. row *************************** Table: orders_partition Create Table: CREATE TABLE `orders_partition` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `Name` char(35) NOT NULL DEFAULT '', `StoreCode` char(3) NOT NULL DEFAULT '', `City` char(35) NOT NULL DEFAULT '', `Mount` int(11) NOT NULL DEFAULT '0', `Type` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`,`Type`) /*T![clustered_index] CLUSTERED */, KEY `idx_StoreCode` (`StoreCode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=2677458 PARTITION BY LIST COLUMNS(`Type`) (PARTITION `p0` VALUES IN (1), PARTITION `p1` VALUES IN (2), PARTITION `p2` VALUES IN (3), PARTITION `p3` VALUES IN (4), PARTITION `p4` VALUES IN (5), PARTITION `p5` VALUES IN (6)) 1 row in set (0.00 sec) ``` + Answer: The `orders_partition` table uses LIST COLUMNS partitioning on the value of the `Type` column. To support partitioning on the `Type` column, the `Type` column must be part of the primary key. 7. Query the `PARTITIONS` table in the `INFORMATION_SCHEMA` database to retrieve the number of rows and the data length for each partition in the orders_partition` table. ```command ANALYZE TABLE orders_partition; SELECT PARTITION_NAME, TABLE_ROWS, DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='orders_partition'; ``` ```output tidb>ANALYZE TABLE orders_partition; Query OK, 0 rows affected, 18 warnings (1.27 sec) tidb>SELECT PARTITION_NAME, TABLE_ROWS, DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='orders_partition'; +----------------+------------+-------------+ | PARTITION_NAME | TABLE_ROWS | DATA_LENGTH | +----------------+------------+-------------+ | p0 | 512000 | 35840000 | | p1 | 512000 | 35840000 | | p2 | 512000 | 35840000 | | p3 | 512000 | 35840000 | | p4 | 512000 | 35840000 | | p5 | 2 | 140 | +----------------+------------+-------------+ 6 rows in set (0.01 sec) ``` 8. Compare the query performance of the `orders_no_partition` and `orders_partition` tables by executing select query test against each of the tables, with the following SQL: ```command SELECT * FROM test.orders_no_partition WHERE Type = 6; SELECT * FROM test.orders_partition WHERE Type = 6; ``` ```output tidb>SELECT * FROM test.orders_no_partition WHERE Type = 6; +---------+----------------------+-----------+----------------------+-------+------+ | ID | Name | StoreCode | City | Mount | Type | +---------+----------------------+-----------+----------------------+-------+------+ | 2560001 | 71add0d7809cbcbcbe70 | 5a7 | ed366dcc516557dc9dd1 | 9718 | 6 | | 2560002 | 729981de40b9d6518610 | ce2 | ec29e10a4e200a85f39d | 2252 | 6 | +---------+----------------------+-----------+----------------------+-------+------+ 2 rows in set (0.74 sec) tidb>SELECT * FROM test.orders_partition WHERE Type = 6; +---------+----------------------+-----------+----------------------+-------+------+ | ID | Name | StoreCode | City | Mount | Type | +---------+----------------------+-----------+----------------------+-------+------+ | 2560002 | 74daf6785e90d21e49fa | d0d | 7264824fb92dff278fc8 | 6453 | 6 | | 2560003 | 34558dcfaf8c36730107 | d79 | 712aa60533e92f925232 | 14 | 6 | +---------+----------------------+-----------+----------------------+-------+------+ 2 rows in set (0.01 sec) ``` + Note：If execute the queries again, you will found there are no different between them in performance, because the data that we just selected is cached. ```output tidb>SELECT * FROM test.orders_no_partition WHERE Type = 6; +---------+----------------------+-----------+----------------------+-------+------+ | ID | Name | StoreCode | City | Mount | Type | +---------+----------------------+-----------+----------------------+-------+------+ | 2560001 | 71add0d7809cbcbcbe70 | 5a7 | ed366dcc516557dc9dd1 | 9718 | 6 | | 2560002 | 729981de40b9d6518610 | ce2 | ec29e10a4e200a85f39d | 2252 | 6 | +---------+----------------------+-----------+----------------------+-------+------+ 1 row in set (0.00 sec) tidb>SELECT * FROM test.orders_partition WHERE Type = 6; +---------+----------------------+-----------+----------------------+-------+------+ | ID | Name | StoreCode | City | Mount | Type | +---------+----------------------+-----------+----------------------+-------+------+ | 2560002 | 74daf6785e90d21e49fa | d0d | 7264824fb92dff278fc8 | 6453 | 6 | | 2560003 | 34558dcfaf8c36730107 | d79 | 712aa60533e92f925232 | 14 | 6 | +---------+----------------------+-----------+----------------------+-------+------+ 2 rows in set (0.00 sec) ``` + Based on your results, what is the effect of partitioning on query performance? + Answer: For queries that use data from a single partition, the performance is better than accessing the same data from a non-partitioned table. But,for queries that must access all partitions to retrieve data, the performance is often worse than accessing the same data from a non-partitioned table. 9. Drop the `orders_no_partition` and `orders_partition` tables. ```command DROP TABLE test.orders_no_partition; DROP TABLE test.orders_partition; ``` ```output tidb>DROP TABLE test.orders_no_partition; Query OK, 0 rows affected (0.31 sec) tidb>DROP TABLE test.orders_partition; Query OK, 0 rows affected (0.34 sec) ``` ## End of Exercises