# Partitioning tables ### Overview + In these exercises, you will create and manage Range partitioning, Range COLUMNS partitioning, List partitioning and List COLUMNS partitioning tables. Afterwards, you will learn about the differences between Hash partitioning and Key partitioning tables, and finally, you will use partition pruning techniques to optimize query performance. ### Notes + The following experiments are designed to run on TiDB v7.5.0. Please install and deploy the TiDB v7.5.0 version database before the exercises. If you use an earlier version of TiDB database, the results may not be consistent with the guide. + Many of the steps require that you type commands in a terminal window. You might find it convenient to launch a terminal window and keep it open for the duration of the exercises. + Your environment may be different with these exercises, including but not limited to `IP`, `port`, `username`, `password` and `directory`. + The `$` in your terminal window is an OS prompt. + The "`tidb>`" or "`tidb>`" in your terminal window is SQL command prompt. + In these exercises, we need 10 machines connected in the network. The `Role` and `IP Address` (represented by environment variables) are as follows: | `Node Role` | `IP Address` | | :----------------- | :-------------------------------------------------------- | | `Monitor 1` | `${HOST_MONITOR1_PRIVATE_IP}, ${HOST_MONITOR1_PUBLIC_IP}` | | `Control Machine` | `${HOST_CM_PRIVATE_IP}, ${HOST_CM_PUBLIC_IP}` | | `TiDB server 1` | `${HOST_DB1_PRIVATE_IP}, ${HOST_DB1_PUBLIC_IP}` | | `TiDB server 2` | `${HOST_DB2_PRIVATE_IP}, ${HOST_DB2_PUBLIC_IP}` | | `TiKV server 1` | `${HOST_KV1_PRIVATE_IP}, ${HOST_KV1_PUBLIC_IP}` | | `TiKV server 2` | `${HOST_KV2_PRIVATE_IP}, ${HOST_KV2_PUBLIC_IP}` | | `TiKV server 3` | `${HOST_KV3_PRIVATE_IP}, ${HOST_KV3_PUBLIC_IP}` | | `PD server 1` | `${HOST_PD1_PRIVATE_IP}, ${HOST_PD1_PUBLIC_IP}` | | `PD server 2` | `${HOST_PD2_PRIVATE_IP}, ${HOST_PD2_PUBLIC_IP}` | | `PD server 3` | `${HOST_PD3_PRIVATE_IP}, ${HOST_PD3_PUBLIC_IP}` | | `TiFlash server 1` | `${HOST_TIFLASH1_PRIVATE_IP}, ${HOST_TIFLASH1_PUBLIC_IP}` | | `Alertmanager` | `${HOST_MONITOR1_PRIVATE_IP}, ${HOST_MONITOR1_PUBLIC_IP}` | | `Grafana` | `${HOST_MONITOR1_PRIVATE_IP}, ${HOST_MONITOR1_PUBLIC_IP}` | | `Prometheus` | `${HOST_MONITOR1_PRIVATE_IP}, ${HOST_MONITOR1_PUBLIC_IP}` | ### Assumptions + You are given the user credentials (for example: the private key) of the exercise environment. + You are given access to 10 machines that have been set up. + Your environment has TiDB cluster deployed and running. ## Exercise 1: Creating and managing Range partitioning, Range COLUMNS partitioning and Range INTERVAL partitioning table. ### 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 15 minutes to complete. ### Tasks 1. Connect to TiDB and create a new non-partitioned table `emp` and insert 36 rows into the `emp` table. 2. Create a new Range partitioning table `employees1` whitout primary key. 3. Create a new COLUMNS Range partitioning table `employees2` whitout primary key. 4. Create a new Range partitioning table `employees3` whit primary key. 5. Create a new COLUMNS Range partitioning table `employees2` whitout primary key. 6. Import the data from the non-partitioned table `emp` into the partitioned tables `employees1`, `employees2`, `employees3`, and `employees4` respectively, and view the partition information. 7. Use the `reorganize partition` command to merge two partitions from `employees1`, `employees2`, `employees3`, and `employees4` into one partition, and view the results after merging. 8. Use the `DROP PARTITION` command to delete unnecessary partitions from `employees1`, `employees2`, `employees3`, and `employees4`. 9. Create two new Range INTERVAL partitioning table `employees5` with Range partitioning and table `employees6` with Range COLUMNS partitioning, and Retrieving partition information of them. ### Solution 0. Login as user `ec2-user` on `Control Machine` (`${HOST_MONITOR1_PUBLIC_IP}`). Enter the following command at the terminal prompt and receive the results shown: ``` ssh -A -o ServerAliveInterval=30 -o ServerAliveCountMax=5 ec2-user@ ... , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| AL2 End of Life is 2025-06-30. ~~ \#/ ___ ~~ 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/ ``` Connect to TiDB. You should provide option `-p` if `root` has a password, otherwise you can omit the `-p` option. (if you don't know how to get the `${HOST_DB1_PRIVATE_IP}` please ask your instructor for help). Enter the following command at the terminal prompt and receive the results shown: ``` $ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root -p mysql> use test; Database changed mysql> prompt tidb> PROMPT set to 'tidb>' ``` 1. Create a new non-partitioned table `emp` and insert 36 rows into the `emp` table. Enter the following command at the terminal prompt and receive the results shown: ```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; ``` ``` 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(2, 'Mott', 'Johnson', '2018-06-01', 2, 401); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO emp VALUES(3, 'Lundeen', 'Williams', '2018-06-22', 3, 201); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO emp VALUES(4, 'Broe', 'Brown', '2018-09-22', 4, 101); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO emp VALUES(5, 'Layla', 'Jones', '2018-10-12', 5, 301); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO emp VALUES(6, 'Tom', 'Garcia', '2019-01-30', 2, 501); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO emp VALUES(7, 'Donald', 'Miller', '2019-02-21', 2, 401); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO emp VALUES(8, 'Kevin', 'Davis', '2019-03-09', 3, 301); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO emp VALUES(9, 'Ken', 'Rodriguez', '2020-11-17', 1, 201); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO emp VALUES(10, 'Bill', 'Martinez', '2020-11-19', 2, 201); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO emp VALUES(11, 'Jack', 'Hernandez', '2020-12-01', 4, 201); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO emp VALUES(12, 'Fran', 'Jones', '2020-12-01', 4, 201); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO emp VALUES(13, 'Mark', 'Wilson', '2021-07-08', 4, 101); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO emp VALUES(14, 'Bob', 'Thomas', '2021-07-08', 4, 101); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO emp VALUES(15, 'Jeff', 'Taylor', '2022-04-03', 5, 101); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO emp VALUES(16, 'Jim', 'Lee', '2022-04-04', 2, 201); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO emp VALUES(17, 'Tim', 'Anderson', '2022-05-10', 2, 301); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO emp VALUES(18, 'Mike', 'Jackson', '2022-05-10', 3, 401); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO emp VALUES(19, 'Benjamin', 'Thompson', '2022-09-24', 1, 501); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO emp VALUES(20, 'Charlotte', 'Clark', '2022-09-24', 1, 801); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO emp VALUES(21, 'Mason', 'Jones', '2022-10-27', 1, 801); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO emp VALUES(22, 'Emma', 'Robinson', '2023-06-15', 2, 301); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO emp VALUES(23, 'Ava', 'Adams', '2023-07-03', 2, 301); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO emp VALUES(24, 'Olivia', 'Lewis', '2023-08-01', 5, 701); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO emp VALUES(25, 'Ava', 'Phillips', '2023-09-02', 3, 701); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO emp VALUES(26, 'Isabella', 'Morgan', '2023-09-03', 2, 101); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO emp VALUES(27, 'Donald', 'Edwards', '2023-09-03', 4, 101); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO emp VALUES(28, 'Somes', 'Alexander', '2023-12-10', 1, 101); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO emp VALUES(29, 'Crabb', 'Ford', '2024-06-30', 2, 401); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO emp VALUES(30, 'Sisika', 'Williams', '2024-06-30', 2, 401); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO emp VALUES(31, 'Noah', 'Anderson', '2024-07-02', 4, 101); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO emp VALUES(32, 'Jacob', 'Martinez', '2025-01-11', 4, 501); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO emp VALUES(33, 'Kim', 'Miller', '2025-01-30', 1, 401); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO emp VALUES(34, 'Jim', 'Smith', '2025-04-24', 2, 501); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO emp VALUES(35, 'Sam', 'Martinez', '2025-05-22', 3, 101); Query OK, 1 row affected (0.00 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` whitout primary key. Enter the following command at the terminal prompt and receive the results shown: ```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 ); ``` ``` 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 (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. ```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 ); ``` ``` 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` whitout primary key. Enter the following command at the terminal prompt and receive the results shown: ```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 ); ``` ``` 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. Any expressions, such as non-COLUMNS partitioning, are not supported.You can use one or more columns as partitioning keys. 4. Create a new Range partitioning table `employees3` whit primary key. Enter the following command at the terminal prompt and receive the results shown: ```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 ); ``` ``` tidb>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 every unique key on the table must use every column in the table's partitioning expression. ```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 ); ``` ``` 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` whit primary key. Enter the following command at the terminal prompt and receive the results shown: ```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 ); ``` ``` 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 every unique key on the table must use every column in the table's partitioning expression. ```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 ); ``` ``` 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` respectively, and view the partition information. Enter the following command at the terminal prompt and receive the results shown: ```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; 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; ``` ``` 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>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) ``` 7. Use the `reorganize partition` command to merge two partitions from `employees1`, `employees2`, `employees3`, and `employees4` into one partition, and view the results after merging. Enter the following command at the terminal prompt and receive the results shown: ```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')); 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; ``` ``` 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>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 | 0 | | employees2 | p0 | 5 | | employees2 | p1 | 3 | | employees2 | p2 | 4 | | employees2 | p3 | 2 | | employees2 | p4 | 7 | | employees2 | p5 | 7 | | employees2 | p8 | 0 | | employees2 | pErr | 0 | | employees3 | p0 | 5 | | employees3 | p1 | 3 | | employees3 | p2 | 4 | | employees3 | p3 | 2 | | employees3 | p4 | 7 | | employees3 | p5 | 7 | | employees3 | p8 | 0 | | employees3 | pErr | 0 | | employees4 | p0 | 5 | | employees4 | p1 | 3 | | employees4 | p2 | 4 | | employees4 | p3 | 2 | | employees4 | p4 | 7 | | employees4 | p5 | 7 | | employees4 | p8 | 0 | | employees4 | pErr | 0 | +------------+----------------+------------+ 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 reorganize adjacent partitions in it." + You can see that the `p6` and `p7` partitions of the four partition tables have been merged into the pErr partition. However, we have found that there is currently no data in the `pErr` partition of the four tables when querying `information_schema.partitions`. Next, we will query the `pErr`` partition data of each table. ```sql SELECT * FROM employees1 PARTITION(pErr); SELECT * FROM employees2 PARTITION(pErr); SELECT * FROM employees3 PARTITION(pErr); SELECT * FROM employees4 PARTITION(pErr); ``` ``` tidb>SELECT * FROM employees1 PARTITION(pErr); +----+--------+----------+------------+-----------+-----------+ | id | fname | lname | hired | dept_code | region_id | +----+--------+----------+------------+-----------+-----------+ | 29 | Crabb | Ford | 2024-06-30 | 2 | 401 | | 30 | Sisika | Williams | 2024-06-30 | 2 | 401 | | 31 | Noah | Anderson | 2024-07-02 | 4 | 101 | | 32 | Jacob | Martinez | 2025-01-11 | 4 | 501 | | 33 | Kim | Miller | 2025-01-30 | 1 | 401 | | 34 | Jim | Smith | 2025-04-24 | 2 | 501 | | 35 | Sam | Martinez | 2025-05-22 | 3 | 101 | | 36 | Candy | Morgan | 2025-08-03 | 1 | 301 | +----+--------+----------+------------+-----------+-----------+ 8 rows in set, 1 warning (0.00 sec) tidb>SELECT * FROM employees2 PARTITION(pErr); +----+--------+----------+------------+-----------+-----------+ | id | fname | lname | hired | dept_code | region_id | +----+--------+----------+------------+-----------+-----------+ | 29 | Crabb | Ford | 2024-06-30 | 2 | 401 | | 30 | Sisika | Williams | 2024-06-30 | 2 | 401 | | 31 | Noah | Anderson | 2024-07-02 | 4 | 101 | | 32 | Jacob | Martinez | 2025-01-11 | 4 | 501 | | 33 | Kim | Miller | 2025-01-30 | 1 | 401 | | 34 | Jim | Smith | 2025-04-24 | 2 | 501 | | 35 | Sam | Martinez | 2025-05-22 | 3 | 101 | | 36 | Candy | Morgan | 2025-08-03 | 1 | 301 | +----+--------+----------+------------+-----------+-----------+ 8 rows in set, 1 warning (0.00 sec) tidb>SELECT * FROM employees3 PARTITION(pErr); +----+--------+----------+------------+-----------+-----------+ | id | fname | lname | hired | dept_code | region_id | +----+--------+----------+------------+-----------+-----------+ | 29 | Crabb | Ford | 2024-06-30 | 2 | 401 | | 30 | Sisika | Williams | 2024-06-30 | 2 | 401 | | 31 | Noah | Anderson | 2024-07-02 | 4 | 101 | | 32 | Jacob | Martinez | 2025-01-11 | 4 | 501 | | 33 | Kim | Miller | 2025-01-30 | 1 | 401 | | 34 | Jim | Smith | 2025-04-24 | 2 | 501 | | 35 | Sam | Martinez | 2025-05-22 | 3 | 101 | | 36 | Candy | Morgan | 2025-08-03 | 1 | 301 | +----+--------+----------+------------+-----------+-----------+ 8 rows in set, 1 warning (0.00 sec) tidb>SELECT * FROM employees4 PARTITION(pErr); +----+--------+----------+------------+-----------+-----------+ | id | fname | lname | hired | dept_code | region_id | +----+--------+----------+------------+-----------+-----------+ | 29 | Crabb | Ford | 2024-06-30 | 2 | 401 | | 30 | Sisika | Williams | 2024-06-30 | 2 | 401 | | 31 | Noah | Anderson | 2024-07-02 | 4 | 101 | | 32 | Jacob | Martinez | 2025-01-11 | 4 | 501 | | 33 | Kim | Miller | 2025-01-30 | 1 | 401 | | 34 | Jim | Smith | 2025-04-24 | 2 | 501 | | 35 | Sam | Martinez | 2025-05-22 | 3 | 101 | | 36 | Candy | Morgan | 2025-08-03 | 1 | 301 | +----+--------+----------+------------+-----------+-----------+ 8 rows in set, 1 warning (0.00 sec) ``` + You can see that the `pErr` partitions of the 4 partitioned tables contain data from the original `p6` and `p7` partitions. So why does the query result of `information_schema.partitions` show 0? Let's try collecting statistics for the 4 tables. ```sql 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; ``` ``` tidb>ANALYZE TABLE employees1; Query OK, 0 rows affected, 16 warnings (1.33 sec) tidb>ANALYZE TABLE employees2; Query OK, 0 rows affected, 16 warnings (1.23 sec) tidb>ANALYZE TABLE employees3; Query OK, 0 rows affected, 16 warnings (1.76 sec) tidb>ANALYZE TABLE employees4; Query OK, 0 rows affected, 16 warnings (1.41 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) ``` + You can see that after reorganizing partitions, it is necessary to collect statistics before the partition can synchronize with the data in information_schema.partitions. 8. Use the `DROP PARTITION` command to delete unnecessary partitions `pErr` from `employees1`, `employees2`, `employees3`, and `employees4`. Enter the following command at the terminal prompt and receive the results shown: ```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; 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; ``` ``` 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>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) ``` 9. Create two new Range INTERVAL partitioning table `employees5` with Range partitioning and table `employees6` with Range COLUMNS partitioning, and Retrieving partition information of them. Enter the following command at the terminal prompt and receive the results shown: ```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; 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; ``` ``` 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>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. 10. Clean up the tables. Enter the following command at the terminal prompt and receive the results shown: ```sql DROP TABLE emp; DROP TABLE employees1; DROP TABLE employees2; DROP TABLE employees3; DROP TABLE employees4; DROP TABLE employees5; DROP TABLE employees6; ``` ``` 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: Swapping a partition and a non-partitioned table by 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 1. Connect to TiDB and create a new non-partitioned table `students`. 2. Check the variables `tidb_enable_list_partition` for creating a new LIST COLUMNS partitioning table. 2. Create a new LIST COLUMNS partitioning table named `students1`. 3. Retrieving partition information of table `students1` and insert 3 rows. 4. Exchange one of the table `students1`‘s partitions to the on-partitioned table `students` and view the result. ### Solution 0. In terminal 1, connect to TiDB and change the current database to `test`. You should provide option `-p` if `root` has a password, otherwise you can omit the `-p` option. Enter the following command at the terminal prompt and receive the results shown: ``` $ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root -p ``` ```sql USE test; ``` ``` tidb> USE test; Database changed ``` 1. Connect to TiDB and create a new non-partitioned table `students`. Enter the following command at the terminal prompt and receive the results shown: ```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 ); ``` ``` 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. Enter the following command at the terminal prompt and receive the results shown: ```sql SHOW VARIABLES LIKE 'tidb_enable_list_partition'; ``` ``` 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`. Enter the following command at the terminal prompt and receive the results shown: ```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')); ``` ``` 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. ```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')); ``` ``` 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 Every unique key on the table must use every column in the table's partitioning expression. ```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')); ``` ``` 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. Create two new roles called `r_mgr` and `r_emp`. Enter the following command at the terminal prompt and receive the results shown: ```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); 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; ``` ``` 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>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 on-partitioned table `students` and view the result. Enter the following command at the terminal prompt and receive the results shown: ```sql ALTER TABLE students1 EXCHANGE PARTITION `p0` WITH TABLE `students`; ``` ``` 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. ```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`; 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 students1; ``` ``` 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>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) ``` 7. Clean up the tables. Enter the following command at the terminal prompt and receive the results shown: ```sql DROP TABLE students; DROP TABLE students1; ``` ``` 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: Comparing Hash partitioning and Key partitioning table. ### 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 1. Connect to TiDB and create a new Hash partitioning table named `employees1`. 2. Insert rows into the table `employees1` and research the distribution of the Hash partitioning table. 3. Create a new Key partitioning table named `employees2`. 4. Insert rows into the table `employees2` and research the distribution of the Key partitioning table. ### Solution 0. In terminal 1, connect to TiDB and change the current database to `test`. You should provide option `-p` if `root` has a password, otherwise you can omit the `-p` option. Enter the following command at the terminal prompt and receive the results shown: ``` $ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root -p ``` ```sql USE test; ``` ``` tidb> USE test; Database changed ``` 1. Connect to TiDB and create a new Hash partitioning table named `employees1`. Enter the following command at the terminal prompt and receive the results shown: ```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; ``` ``` 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 research the distribution of the Hash partitioning table. Enter the following command at the terminal prompt and receive the results shown: ```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); ``` ``` 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(2, 'Tim', 'Anderson', '2023-05-10', 2); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO employees1 VALUES(3, 'Mike', 'Jackson', '2023-05-10', 3); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO employees1 VALUES(4, 'Benjamin', 'Thompson', '2023-09-24', 1); Query OK, 1 row affected (0.00 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 inerted have same YEAR(hired) value, then we research the distribution of the date. ```sql 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; ``` ``` 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) ``` + We found that all rows are stored in partition `p3`. Why? + We 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); ``` ``` tidb>TRUNCATE TABLE employees1; Query OK, 0 rows affected (0.53 sec) tidb>INSERT INTO employees1 VALUES(1, 'Jim', 'Lee', '2020-04-04', 2); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO employees1 VALUES(2, 'Tim', 'Anderson', '2021-05-10', 2); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO employees1 VALUES(3, 'Mike', 'Jackson', '2022-05-10', 3); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO employees1 VALUES(4, 'Benjamin', 'Thompson', '2023-09-24', 1); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO employees1 VALUES(5, 'Charlotte', 'Clark', '2024-09-24', 1); Query OK, 1 row affected (0.01 sec) ``` + We found that all rows inerted have different YEAR(hired) value, then we research the distribution of the date. ```sql 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; ``` ``` 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) ``` + We found that there is one row each partition. 3. Create a new Key partitioning table named `employees1`. Enter the following command at the terminal prompt and receive the results shown: ```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; ``` ``` 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 research the distribution of the Key partitioning table. Enter the following command at the terminal prompt and receive the results shown: ```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); ``` ``` 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(2, 'Tim', 'Anderson', '2023-05-10', 2); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO employees2 VALUES(3, 'Mike', 'Jackson', '2023-05-10', 3); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO employees2 VALUES(4, 'Benjamin', 'Thompson', '2023-09-24', 1); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO employees2 VALUES(5, 'Charlotte', 'Clark', '2023-09-24', 1); Query OK, 1 row affected (0.00 sec) ``` + We found that all rows inserted into `employees2` have different `hired` value, but the VALUES of YEAR(hired) are same, then we research the distribution of the date. ```sql 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; ``` ``` 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. + We 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); ``` ``` 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(2, 'Tim', 'Anderson', '2021-05-10', 2); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO employees2 VALUES(3, 'Mike', 'Jackson', '2021-05-10', 3); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO employees2 VALUES(4, 'Benjamin', 'Thompson', '2021-05-10', 1); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO employees2 VALUES(5, 'Charlotte', 'Clark', '2021-05-10', 1); Query OK, 1 row affected (0.00 sec) ``` + We found that all rows inserted into `employees2` have the same `hired` value, then we research the distribution of the date. ```sql 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; ``` ``` 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) ``` + We found that all rows are stored in partition `p4`. Why? + We 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); ``` ``` 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(2, 'Tim', 'Anderson', '2021-05-10', 2); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO employees2 VALUES(3, 'Mike', 'Jackson', '2022-05-10', 3); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO employees2 VALUES(4, 'Benjamin', 'Thompson', '2023-09-24', 1); 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) ``` + We found that all rows inserted into `employees2` have different `hired` value, then we research the distribution of the date. ```sql 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; ``` ``` 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) + The rows are distribute in 4 partitions. 5. Clean up the tables. Enter the following command at the terminal prompt and receive the results shown: ```sql DROP TABLE employees1; DROP TABLE employees2; ``` ``` 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: Comparing performance of partitioning table and non partitioned table ### Overview + In this practice, you evaluate the effect of partitioning tables on performance. To accomplish this objective, you perform the following tasks: + 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 1. Connect to TiDB and create a new non-partitioned table named `orders_no_partition` under test database. 2. Exit from TiDB and execute commands for inserting rows into the table `orders_no_partition`. 3. Connect to TiDB and create a new LIST COLUMNS partitioning table named `orders_partition` under test database. 4. Exit from TiDB and execute commands for inserting rows into the table `orders_partition`. 5. Connect to TiDB and insert two rows into each of the `orders_no_partition` and `orders_partition` tables. 6. Execute SHOW `TABLE STATUS` on the `orders_no_partition` table. What value does the `Create_options` column in the output contain? 7. Execute SHOW `TABLE STATUS` on the `orders_partition` table. What value does the `Create_options` column in the output contain? 8. 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? 9. 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. 10. 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;` 11. Based on your results in step 10, what is the effect of partitioning on query performance? 12. Drop the `orders_no_partition` and `orders_partition` tables. 13. Close all open terminal windows. ### Solution 0. In terminal 1, connect to TiDB and change the current database to `test`. You should provide option `-p` if `root` has a password, otherwise you can omit the `-p` option. Enter the following command at the terminal prompt and receive the results shown: ``` $ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root -p ``` ```sql USE test; ``` ``` tidb> USE test; Database changed ``` 1. Create a new non-partitioned table named `orders_no_partition` under test database. Enter the following command at the terminal prompt and receive the results shown: ```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) ); ``` ``` 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) ``` 2. Exit from TiDB and execute commands for inserting rows into the table `orders_no_partition`. Enter the following command at the terminal prompt and receive the results shown: ``` tidb>EXIT for i in `seq 1000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -p -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} -p -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} -p -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} -p -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} -p -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} -p -e "INSERT INTO test.orders_no_partition SELECT NULL, Name, StoreCode, City,Mount, Type FROM test.orders_no_partition;"; done; ``` ``` tidb>EXIT Bye $ for i in `seq 1000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -p -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. 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. $ ``` 3. Create a new LIST COLUMNS partition table named `orders_partition` under test database. Enter the following command at the terminal prompt and receive the results shown: ```sql USE test; 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) ); ``` ``` tidb>use test; Database changed 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) ``` 4. Exit from TiDB and execute commands for inserting rows into the table `orders_partition`. Enter the following command at the terminal prompt and receive the results shown: ``` tidb>EXIT for i in `seq 1000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -p -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} -p -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} -p -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} -p -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} -p -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} -p -e "INSERT INTO test.orders_partition SELECT NULL, Name, StoreCode, City,Mount, Type from test.orders_partition;"; done; ``` ``` tidb>EXIT Bye $ for i in `seq 1000`; do mysql -uroot -P4000 -h${HOST_DB1_PRIVATE_IP} -p -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. 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. mysql: [Warning] Using a password on the command line interface can be insecure. $ ``` 6. Connect to TiDB and insert two rows into each of the `orders_no_partition` and `orders_partition` tables.Enter the following command at the terminal prompt and receive the results shown: ```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); ``` ``` 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. ```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); ``` ``` 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) ``` 6. Execute SHOW `TABLE STATUS` on the `orders_no_partition` table. What value does the `Create_options` column in the output contain? Enter the following command at the terminal prompt and receive the results shown: ```sql SHOW TABLE STATUS LIKE 'orders_no_partition'\G; ``` ``` 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: Ther `Create_options` column in the `SHOW TABLE STATUS` output for the 'orders_no_partition' table is empty. 7. Execute SHOW `TABLE STATUS` on the `orders_partition` table. What value does the `Create_options` column in the output contain? Enter the following command at the terminal prompt and receive the results shown: ```sql SHOW TABLE STATUS LIKE 'orders_partition'\G; ``` ``` 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: Ther `Create_options` column in the `SHOW TABLE STATUS` output for the 'orders_partition' table is partitioned. 8. 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? Enter the following command at the terminal prompt and receive the results shown: ```sql SHOW CREATE TABLE orders_no_partition\G SHOW CREATE TABLE orders_partition\G ``` ``` 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. 9. 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. Enter the following command at the terminal prompt and receive the results shown: ```sql SELECT PARTITION_NAME, TABLE_ROWS, DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='orders_partition'; ``` ``` 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) ``` 10. 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: ```sql SELECT * FROM test.orders_no_partition WHERE Type = 6; SELECT * FROM test.orders_partition WHERE Type = 6; ``` ``` 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. ``` 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) ``` 11. Based on your results in step 11, 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. 12. Drop the `orders_no_partition` and `orders_partition` tables. Enter the following command at the terminal prompt and receive the results shown: ```sql DROP TABLE test.orders_no_partition; DROP TABLE test.orders_partition; ``` ``` 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) ``` 13. Close all open terminal windows. ## End of Exercises