--- title: "Partitioned Tables" version: "v2" status: "GA" description: "In this hands-on lab, 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." --- # Lab 2: Partitioned Tables ## Overview >[!NOTE] > This hands-on lab is also included in the [TiDB Certification Training: TiDB SQL Tuning](https://www.pingcap.com/education/course-tidb-sql-tuning/) course. In this lab, you will create and manage Range partitioning, Range COLUMNS partitioning, List partitioning and List COLUMNS partitioned tables. You will learn about the differences between Hash partitioning and Key partitioned tables. Finally, you will compare the performance between partitioned and non-partitioned table. > [!TIP] > Once the lab environment is deployed, click **Next** on the bottom bar to begin **Module 1**. ### Duration + This lab should take you approximately 60 minutes to complete. ## Module 1: RANGE, RANGE COLUMN, and RANGE INTERVAL Partitioned Tables + In this exercise, you will create and manage Range partitioning, Range COLUMNS partitioning and Range INTERVAL partitioned table. ### Step 1. Log in to the remote Linux + Click here - [Terminal 1](ssh://monitor1-ssh#1) to open a new terminal connected to the virtual machine assigned for your hands-on lab. + The banner displayed after logging in appears as follows. ```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/ ``` ### Step 2. Connect to TiDB and create a non-partitioned table `emp` ```bash mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```sql use test; ``` ```output Database changed ``` ```sql 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 +----------+ | COUNT(*) | +----------+ | 36 | +----------+ 1 row in set (0.00 sec) ``` ### Step 3. Create a range partitioned table `employees1` without PK ```sql 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 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. ```sql 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 Query OK, 0 rows affected (0.13 sec) ``` ### Step 4. Create a COLUMNS Range partitioned table `employees2` without PK ```sql 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 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. ### Step 5. Create a Range partitioned table `employees3` with PK ```sql 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 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. ```sql 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 Query OK, 0 rows affected (0.13 sec) ``` ### Step 6. Create a COLUMNS Range partitioned table `employees4` with PK ```sql 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 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. ```sql 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 Query OK, 0 rows affected (0.14 sec) ``` ### Step 7. Import data into partitioned tables + 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. + Note that the `ANALYZE TABLE` command may need a few seconds to run. ```sql 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 +------------+----------------+------------+ | 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. ```sql 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 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 +------------+----------------+------------+ | 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. ### Step 8. Drop partitions + Use the `DROP PARTITION` command to delete unnecessary partitions `pErr` from `employees1`, `employees2`, `employees3`, and `employees4`. ```sql 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 +------------+----------------+------------+ | 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) ``` ### Step 9. Create a Range INTERVAL and a Range COLUMNS partitioned table ```sql 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 +------------+-----------------+------------+ | 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. ### Step 10. Clean up the tables ```sql DROP TABLE emp; DROP TABLE employees1; DROP TABLE employees2; DROP TABLE employees3; DROP TABLE employees4; DROP TABLE employees5; DROP TABLE employees6; ``` ```output Query OK, 0 rows affected (0.81 sec) ``` ## Module 2: Exchanging Partitions + In this exercise, we will create a List COLUMNS partitioned table, insert data into the table, and then use EXCHANGE PARTITION to exchange the data between the partitions and a non-partitioned table. ### Step 1. Create a non-partitioned table `students` ```sql 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) ``` ### Step 2. Check the value of the variables `tidb_enable_list_partition` ```sql 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. ### Step 3. Create a new LIST COLUMNS partitioned table `students1` ```sql 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 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. ```sql 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 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. ```sql 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 Query OK, 0 rows affected (0.54 sec) ``` ### Step 4. Insert some values into the table `students1` ```sql 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 +------------+----------------+-------------------------------+------------+ | 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) +------------+----------------+-------------------------------+------------+ | 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. ### Step 5. Exchange the partitions + Exchange one of the table `students1`‘s partitions to the non-partitioned table `students`. ```sql ALTER TABLE students1 EXCHANGE PARTITION `p0` WITH TABLE `students`; ``` ```output 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`. ```sql 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 +------------+----------------+-------------------------------+------------+ | 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) +----+-------+--------+---------+------------+------+ | 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) ``` ### Step 6. Clean up the tables ```sql DROP TABLE students; DROP TABLE students1; ``` ```output Query OK, 0 rows affected (0.52 sec) ``` ## Module 3: Hash and Key Partitioned Tables + In this exercise, we will create a Hash partitioned table and a Key partitioned table. After inserting the same data into both tables, we will compare the distribution of the data. ### Step 1. Create a new Hash partitioned table `employees1` ```sql 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 Query OK, 0 rows affected (0.54 sec) ``` ### Step 2. Insert some data into `employees1` ```sql 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 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. ```sql 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 +------------+----------------+-----------------------+------------+ | 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. ```sql 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 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. ```sql 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 +------------+----------------+-----------------------+------------+ | 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. ### Step 3. Create a new Key partitioned table `employees2` ```sql 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 Query OK, 0 rows affected (0.54 sec) ``` ### Step 4. Insert some data into `employees2` ```sql 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 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. ```sql 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 +------------+----------------+-----------------------+------------+ | 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 ```sql 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 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. ```sql 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 +------------+----------------+-----------------------+------------+ | 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. ```sql 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 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. ```sql 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 +------------+----------------+-----------------------+------------+ | 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) ``` ### Step 5. Clean up the tables ```sql DROP TABLE employees1; DROP TABLE employees2; ``` ```output Query OK, 0 rows affected (0.53 sec) ``` ## Module 4: Performance Comparison + In this exercise, we evaluate the effect of partitioned tables on performance. + Partition an existing table by using LIST COLUMNS partitioning. + Execute queries against both the original and partitioned tables to compare performance. ### Step 1. Created a non-partitioned table `orders_no_partition` ```sql 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 Query OK, 0 rows affected (0.14 sec) ``` ```sql EXIT ``` ```bash 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 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. ``` ### Step 2. Create a LIST COLUMNS partition table `orders_partition` ```bash mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```sql use test; ``` ```sql 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 Query OK, 0 rows affected (0.15 sec) ``` ```sql EXIT ``` ```bash 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 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. ``` ### Step 3. Insert data into the `orders_no_partition` and `orders_partition` ```bash mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```sql use test; ``` ```output Database changed ``` ```sql 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_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. ```sql 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 Query OK, 1 row affected (0.00 sec) ``` ### Step 4. Check the status of the `orders_no_partition` table + What value does the `Create_options` column in the output contain? ```sql 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. ### Step 5. Check the status of the `orders_partition` table + What value does the `Create_options` column in the output contain? ```sql 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. ### Step 6. Compare the `CREATE TABLE` statements of the two tables + How is the `orders_partition` table partitioned? ```sql SHOW CREATE TABLE orders_no_partition\G SHOW CREATE TABLE orders_partition\G ``` ```output *************************** 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) *************************** 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. ### Step 7. Check the number of rows in each partition for `orders_partition` + 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. ```sql ANALYZE TABLE orders_partition; SELECT PARTITION_NAME, TABLE_ROWS, DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='orders_partition'; ``` ```output +----------------+------------+-------------+ | 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) ``` ### Step 8. Compare the performance ```sql SELECT * FROM test.orders_no_partition WHERE Type = 6; SELECT * FROM test.orders_partition WHERE Type = 6; ``` ```output +---------+----------------------+-----------+----------------------+-------+------+ | 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) +---------+----------------------+-----------+----------------------+-------+------+ | 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. ### Step 9. Drop the `orders_no_partition` and `orders_partition` tables ```sql DROP TABLE test.orders_no_partition; DROP TABLE test.orders_partition; ``` ```output Query OK, 0 rows affected (0.34 sec) ``` ## End of Exercises