# Optimizing Queries ### Overview + In this practice, you determine the optimizer's strategy for a particular query. Add indexes to the tables that the query accesses and evaluate the effect on the query execution plan. Improve the performance of a specific query. ### Notes + The following experiments are designed to run on TiDB v7.5. Please install and deploy the TiDB v7.5. 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}` | + Exercise 1 and Exercise 2 requires the experimental data imported in Exercise 0 of Chapter03 Optimizer, so please complete Exercise 0 of Chapter03 Optimizer before starting Exercise 1 and Exercise 1. ### 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: Indexing for Query Performance + In this exercise, you add indexes to the tables that the query accesses and evaluate the effect on the query execution plan. ### Duration + This exercise should take you approximately 20 minutes to complete. ### Tasks 1. Open a Linux terminal window and start an interactive TiDB session. 2. Make `universe` the current database. 3. Execute the following statement to view the EXPLAIN output for a query against the `planetarySystem` table: ```sql CREATE INDEX idx_pl_name on planetarySystem(pl_name); ANALYZE TABLE planetarySystem; EXPLAIN SELECT pl_name, hostname FROM planetarySystem WHERE pl_name='HAT-P-42 b' and hostname='HAT-P-42'\G; ``` + What is the access method for this query? + What cost did the optimizer calculate for this query? + Which columns must TiDB access to resolve the query? + Does this access strategy result in good performance? Why? 4. Drop the `idx_pl_name` index from the `planetarySystem` table. 5. Create a covering index called `idx_pl_name_hostname` that indexes the appropriate columns in the `planetarySystem` table so that the query that you examined in the previous step executes without accessing the table rows. ```sql CREATE INDEX idx_pl_name_hostname on planetarySystem(pl_name, hostname); ANALYZE TABLE planetarySystem; ``` 6. Re-issue the EXPLAIN statement from step 3. Explain the difference in the output. 7. Drop the `idx_pl_name_hostname` index from the `planetarySystem` table. 8. Add an index `idx_pl_name_hostname_ref` index on the `pl_name`, `hostname` and `ref_id` columns of the `planetarySystem` table. ```sql CREATE INDEX idx_pl_name_hostname_ref ON planetarySystem(pl_name, hostname, ref_id); ANALYZE TABLE planetarySystem; ``` 9. Execute the following statement to view the EXPLAIN output for a query against the `planetarySystem` table: ```sql EXPLAIN SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where hostname='11 Com' AND ref_id=2048\G; ``` + What is the access method for this query? + Is the index `idx_pl_name_hostname_ref` helpful for improving the performance of this query? Why? 10. Execute the following statement to view the EXPLAIN output for a query against the `planetarySystem` table: ```sql EXPLAIN SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where pl_name='11 Com b' AND ref_id=2048\G; ``` + What is the access method for this query? + What cost did the optimizer calculate for this query? + Which columns must TiDB access to resolve the query? + Is the index `idx_pl_name_hostname_ref` helpful for improving the performance of this query? Why? 11. Execute the following statement to view the EXPLAIN output for a query against the `planetarySystem` table: ```sql EXPLAIN SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where pl_name='11 Com b' AND hostname='11 Com' AND ref_id=2048\G; ``` + What is the access method for this query? + What cost did the optimizer calculate for this query? + Which columns must TiDB access to resolve the query? + Is the index `idx_pl_name_hostname_ref` helpful for improving the performance of this query? Why? 12. Execute the following statement to view the EXPLAIN output for a query against the `planetarySystem` table: ```sql EXPLAIN SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where pl_name='11 Com b' AND hostname like '11%' AND ref_id=2048\G; ``` + What is the access method for this query? + Which columns must TiDB access to resolve the query? + Is the index `idx_pl_name_hostname_ref` helpful for improving the performance of this query? Why? 13. Execute the following statement to view the EXPLAIN output for a query against the `planetarySystem` table: ```sql EXPLAIN SELECT id, pl_name, hostname FROM planetarySystem WHERE hostname = '11 Com' AND ref_id=2048\G; ``` + What is the access method for this query? + Is the index `idx_pl_name_hostname_ref` helpful for improving the performance of this query? Why? + Does this access strategy result in good performance? Why? 14. Drop the `idx_pl_name_hostname_ref` index from the `planetarySystem` table. 15. Add index `idx_ref_id` on the `ref_id` columns of the `planetarySystem` table. ```sql CREATE INDEX idx_ref_id ON planetarySystem(ref_id); ANALYZE TABLE planetarySystem; ``` 16. Execute the following statement to view the EXPLAIN output for a query against the `planetarySystem` table: ```sql EXPLAIN SELECT id, pl_name, hostname FROM planetarySystem WHERE id=835868090839969012 OR ref_id=2048\G; ``` + What is the access method for this query? + Does this access strategy result in good performance? Why? 17. Drop the `idx_ref_id` index from the `planetarySystem` table. 18. Leave the terminal window with the TiDB session open for the next practice. ### Solution 1. 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: ``` $ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root -p mysql> prompt tidb> PROMPT set to 'tidb>' ``` 2. Make `universe` the current database. Enter the following statement at the TiDB prompt and receive the results shown: ```sql use universe; ``` ``` tidb>use universe; Database changed ``` 3. Execute the following statement to view the EXPLAIN output for a query against the `planetarySystem` table. Enter the following statement at the TiDB prompt and receive the results shown: ```sql CREATE INDEX idx_pl_name on planetarySystem(pl_name); ANALYZE TABLE planetarySystem; EXPLAIN SELECT pl_name, hostname FROM planetarySystem WHERE pl_name='HAT-P-42 b' and hostname='HAT-P-42'; ``` ``` tidb>CREATE INDEX idx_pl_name on planetarySystem(pl_name); Query OK, 0 rows affected (2.93 sec) tidb>ANALYZE TABLE planetarySystem; Query OK, 0 rows affected, 1 warning (2.77 sec) tidb>EXPLAIN SELECT pl_name, hostname FROM planetarySystem WHERE pl_name='HAT-P-42 b' and hostname='HAT-P-42'; +-------------------------------+---------+-----------+---------------------------------------------------+-----------------------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------------+---------+-----------+---------------------------------------------------+-----------------------------------------------------+ | IndexLookUp_11 | 0.00 | root | | | | ├─IndexRangeScan_8(Build) | 5.62 | cop[tikv] | table:planetarySystem, index:idx_pl_name(pl_name) | range:["HAT-P-42 b","HAT-P-42 b"], keep order:false | | └─Selection_10(Probe) | 0.00 | cop[tikv] | | eq(universe.planetarysystem.hostname, "HAT-P-42") | | └─TableRowIDScan_9 | 5.62 | cop[tikv] | table:planetarySystem | keep order:false | +-------------------------------+---------+-----------+---------------------------------------------------+-----------------------------------------------------+ 4 rows in set (0.00 sec) ``` + What is the access method for this query? Answer: IndexLookUp and IndexRangeScan. + What cost did the optimizer calculate for this query? Answer: 5.62 rows. + Which columns must TiDB access to resolve the query? Answer: `pl_name` and `hostname` columns. + Does this access strategy result in good performance? Why? Answer: Maybe. The IndexLookUp and IndexRangeScan access method indicates that TiDB cannot select a single row based on the key value. Performance is good only if the key used by the optimizer matches only a few rows. 4. Drop the `idx_pl_name` index from the `planetarySystem` table. Enter the following statement at the TiDB prompt and receive the results shown: ```sql DROP INDEX idx_pl_name ON planetarySystem; ``` ``` tidb>DROP INDEX idx_pl_name ON planetarySystem; Query OK, 0 rows affected (0.36 sec) ``` 5. Create a covering index called `idx_pl_name_hostname` that indexes the appropriate columns in the `planetarySystem` table so that the query that you examined in the previous step executes without accessing the table rows. Enter the following statement at the TiDB prompt and receive the results shown: ```sql CREATE INDEX idx_pl_name_hostname on planetarySystem(pl_name, hostname); ANALYZE TABLE planetarySystem; ``` ``` tidb>CREATE INDEX idx_pl_name_hostname on planetarySystem(pl_name, hostname); Query OK, 0 rows affected (3.10 sec) tidb>ANALYZE TABLE planetarySystem; Query OK, 0 rows affected, 1 warning (5.23 sec) ``` 6. Re-issue the EXPLAIN statement from step 3. Explain the difference in the output. Enter the following statement at the TiDB prompt and receive the results shown: ```sql EXPLAIN SELECT pl_name, hostname FROM planetarySystem WHERE pl_name='HAT-P-42 b' and hostname='HAT-P-42'; ``` ``` tidb>EXPLAIN SELECT pl_name, hostname FROM planetarySystem WHERE pl_name='HAT-P-42 b' and hostname='HAT-P-42'; +------------------------+---------+-----------+----------------------------------------------------------------------+---------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +------------------------+---------+-----------+----------------------------------------------------------------------+---------------------------------------------------------------------------+ | IndexReader_6 | 5.62 | root | | index:IndexRangeScan_5 | | └─IndexRangeScan_5 | 5.62 | cop[tikv] | table:planetarySystem, index:idx_pl_name_hostname(pl_name, hostname) | range:["HAT-P-42 b" "HAT-P-42","HAT-P-42 b" "HAT-P-42"], keep order:false | +------------------------+---------+-----------+----------------------------------------------------------------------+---------------------------------------------------------------------------+ 2 rows in set (0.01 sec) ``` + Explain the difference in the output. Answer: The query now uses the idx_pl_name_hostname index to resolve the query, without having to access the table rows. The access method for this query is IndexReader and IndexRangeScan. 7. Drop the `idx_pl_name_hostname` index from the `planetarySystem` table. Enter the following statement at the TiDB prompt and receive the results shown: ```sql DROP INDEX idx_pl_name_hostname ON planetarySystem; ``` ``` tidb>DROP INDEX idx_pl_name_hostname ON planetarySystem; Query OK, 0 rows affected (0.37 sec) ``` 8. Add an index `idx_pl_name_hostname_ref` index on the `pl_name`, `hostname` and `ref_id` columns of the `planetarySystem` table. Enter the following statement at the TiDB prompt and receive the results shown: ```sql CREATE INDEX idx_pl_name_hostname_ref ON planetarySystem(pl_name, hostname, ref_id); ANALYZE TABLE planetarySystem; ``` ``` tidb>CREATE INDEX idx_pl_name_hostname_ref ON planetarySystem(pl_name, hostname, ref_id); Query OK, 0 rows affected (3.09 sec) tidb>ANALYZE TABLE planetarySystem; Query OK, 0 rows affected, 1 warning (2.88 sec) ``` 9. Execute the following statement to view the EXPLAIN output for a query against the `planetarySystem` table. Enter the following statement at the TiDB prompt and receive the results shown: ```sql EXPLAIN SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where hostname='11 Com' AND ref_id=2048; ``` ``` tidb>EXPLAIN SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where hostname='11 Com' AND ref_id=2048; +---------------------------+----------+-----------+-----------------------+-----------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------------+----------+-----------+-----------------------+-----------------------------------------------------------------------------------------------------------------------------------------+ | Projection_4 | 0.00 | root | | universe.planetarysystem.pl_name, universe.planetarysystem.hostname, universe.planetarysystem.sy_snum, universe.planetarysystem.sy_pnum | | └─TableReader_7 | 0.00 | root | | data:Selection_6 | | └─Selection_6 | 0.00 | cop[tikv] | | eq(universe.planetarysystem.hostname, "11 Com"), eq(universe.planetarysystem.ref_id, 2048) | | └─TableFullScan_5 | 34713.00 | cop[tikv] | table:planetarySystem | keep order:false | +---------------------------+----------+-----------+-----------------------+-----------------------------------------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) ``` + What is the access method for this query? Answer: TableReader and TableFullScan + Is the index `idx_pl_name_hostname_ref` helpful for improving the performance of this query? Why? Answer: No. This is because the index `idx_pl_name_hostname_ref` is composed in the order of `pl_name`, `hostname`, and `ref_id`, so our queries need to be used in the same order as the index, which is `pl_name`, `hostname`, and `ref_id`. In other words, only if the conditions related to the column `pl_name appear` in the query, the conditions related to the `hostname` and `ref_id` columns will use the index `idx_pl_name_hostname_ref`. Only when the conditions related to the `pl_name` and `hostname` columns appear in the query, the conditions related to the `ref_id` column will use the index `idx_pl_name_hostname_ref`. 10. Execute the following statement to view the EXPLAIN output for a query against the `planetarySystem` table. Enter the following statement at the TiDB prompt and receive the results shown: ```sql EXPLAIN SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where pl_name='11 Com b' AND ref_id=2048; ``` ``` tidb>EXPLAIN SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where pl_name='11 Com b' AND ref_id=2048; +---------------------------------+---------+-----------+----------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------------------+---------+-----------+----------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+ | Projection_4 | 0.00 | root | | universe.planetarysystem.pl_name, universe.planetarysystem.hostname, universe.planetarysystem.sy_snum, universe.planetarysystem.sy_pnum | | └─IndexLookUp_11 | 0.00 | root | | | | ├─Selection_10(Build) | 0.00 | cop[tikv] | | eq(universe.planetarysystem.ref_id, 2048) | | │ └─IndexRangeScan_8 | 5.62 | cop[tikv] | table:planetarySystem, index:idx_pl_name_hostname_ref(pl_name, hostname, ref_id) | range:["11 Com b","11 Com b"], keep order:false | | └─TableRowIDScan_9(Probe) | 0.00 | cop[tikv] | table:planetarySystem | keep order:false | +---------------------------------+---------+-----------+----------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+ 5 rows in set (0.00 sec) ``` + What is the access method for this query? Answer: IndexLookUp and IndexRangeScan. + What cost did the optimizer calculate for this query? Answer: 5.62. + Which columns must TiDB access to resolve the query? Answer: `pl_name`. + Is the index `idx_pl_name_hostname_ref` helpful for improving the performance of this query? Why? Answer: Yes. But the query involves conditions related to the column `pl_name`, the index `idx_pl_name_hostname_ref` will be used, but the conditions related to the column `ref_id` will not use an index, because the conditions related to column `hostname` did not appear in the query. We can see from the second line of the EXPLAIN output, in the Selection section, that the condition related to `ref_id` is completed separately after the IndexRangeScan. 11. Execute the following statement to view the EXPLAIN output for a query against the `planetarySystem` table. Enter the following statement at the TiDB prompt and receive the results shown: ```sql EXPLAIN SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where pl_name='11 Com b' AND hostname='11 Com' AND ref_id=2048; ``` ``` tidb>EXPLAIN SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where pl_name='11 Com b' AND hostname='11 Com' AND ref_id=2048; +---------------------------------+---------+-----------+----------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------------------+---------+-----------+----------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+ | Projection_4 | 1.00 | root | | universe.planetarysystem.pl_name, universe.planetarysystem.hostname, universe.planetarysystem.sy_snum, universe.planetarysystem.sy_pnum | | └─IndexLookUp_10 | 1.00 | root | | | | ├─IndexRangeScan_8(Build) | 1.00 | cop[tikv] | table:planetarySystem, index:idx_pl_name_hostname_ref(pl_name, hostname, ref_id) | range:["11 Com b" "11 Com" 2048,"11 Com b" "11 Com" 2048], keep order:false | | └─TableRowIDScan_9(Probe) | 1.00 | cop[tikv] | table:planetarySystem | keep order:false | +---------------------------------+---------+-----------+----------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) ``` + What is the access method for this query? Answer: IndexLookUp and IndexRangeScan. + What cost did the optimizer calculate for this query? Answer: 1.00. + Which columns must TiDB access to resolve the query? `pl_name`, `hostname` and `ref_id` columns. + Is the index `idx_pl_name_hostname_ref` helpful for improving the performance of this query? Why? Yes. The columns `pl_name`, `hostname`, and `ref_id` in the `index idx_pl_name_hostname_ref` all appear in the query. 12. Execute the following statement to view the EXPLAIN output for a query against the `planetarySystem` table. Enter the following statement at the TiDB prompt and receive the results shown: ```sql EXPLAIN SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where pl_name='11 Com b' AND hostname like '11%' AND ref_id=2048; ``` ``` tidb>EXPLAIN SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where pl_name='11 Com b' AND hostname like '11%' AND ref_id=2048; +---------------------------------+---------+-----------+----------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------------------+---------+-----------+----------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+ | Projection_4 | 0.00 | root | | universe.planetarysystem.pl_name, universe.planetarysystem.hostname, universe.planetarysystem.sy_snum, universe.planetarysystem.sy_pnum | | └─IndexLookUp_11 | 0.00 | root | | | | ├─Selection_10(Build) | 0.00 | cop[tikv] | | eq(universe.planetarysystem.ref_id, 2048) | | │ └─IndexRangeScan_8 | 0.00 | cop[tikv] | table:planetarySystem, index:idx_pl_name_hostname_ref(pl_name, hostname, ref_id) | range:["11 Com b" "11","11 Com b" "12"), keep order:false | | └─TableRowIDScan_9(Probe) | 0.00 | cop[tikv] | table:planetarySystem | keep order:false | +---------------------------------+---------+-----------+----------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+ 5 rows in set (0.00 sec) ``` + What is the access method for this query? Answer: IndexLookUp and IndexRangeScan. + Which columns must TiDB access to resolve the query? Anserver: `pl_name` and `hostname` columns. + Is the index `idx_pl_name_hostname_ref` helpful for improving the performance of this query? Why? Answer: Yes. Although the columns `pl_name`, `hostname`, and `ref_id` in the index `idx_pl_name_hostname_ref` all appear in the query, we still cannot use the column `ref_id` in the index because the condition on the `hostname` column is not an equality query, but rather a range query. We can see from the second line of the EXPLAIN output, in the Selection section, that the condition related to `ref_id` is completed separately after the IndexRangeScan. 13. Execute the following statement to view the EXPLAIN output for a query against the `planetarySystem` table. Enter the following statement at the TiDB prompt and receive the results shown: ```sql EXPLAIN SELECT id, pl_name, hostname FROM planetarySystem WHERE hostname = '11 Com' AND ref_id=2048; ``` ``` tidb>EXPLAIN SELECT id, pl_name, hostname FROM planetarySystem WHERE hostname = '11 Com' AND ref_id=2048; +---------------------------+----------+-----------+----------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------------+----------+-----------+----------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------+ | Projection_4 | 0.00 | root | | universe.planetarysystem.id, universe.planetarysystem.pl_name, universe.planetarysystem.hostname | | └─IndexReader_10 | 0.00 | root | | index:Selection_9 | | └─Selection_9 | 0.00 | cop[tikv] | | eq(universe.planetarysystem.hostname, "11 Com"), eq(universe.planetarysystem.ref_id, 2048) | | └─IndexFullScan_8 | 34713.00 | cop[tikv] | table:planetarySystem, index:idx_pl_name_hostname_ref(pl_name, hostname, ref_id) | keep order:false | +---------------------------+----------+-----------+----------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------+ 4 rows in set (0.01 sec) ``` + What is the access method for this query? Answer: IndexReader and IndexFullScan. + Is the index `idx_pl_name_hostname_ref` helpful for improving the performance of this query? Why? Yes. Although the column `pl_name` in the index `idx_pl_name_hostname_ref` is not used in the query condition, since the SELECT statement includes `id`, `pl_name`, and `hostname` columns, all of which are included in the index `idx_pl_name_hostname_ref`, we can use IndexFullScan. + Does this access strategy result in good performance? Why? Anserver: Probably not. Because as the data volume increases, the amount of index data that IndexFullScan needs to scan will also increase, which will result in a decrease in performance. 14. Drop the `idx_pl_name_hostname_ref` index from the `planetarySystem` table. Enter the following statement at the TiDB prompt and receive the results shown: ```sql DROP INDEX idx_pl_name_hostname_ref ON planetarySystem; ``` ``` tidb>DROP INDEX idx_pl_name_hostname_ref ON planetarySystem; Query OK, 0 rows affected (0.36 sec) ``` 15. Add index `idx_ref_id` on the `ref_id` columns of the `planetarySystem` table. Enter the following statement at the TiDB prompt and receive the results shown: ```sql CREATE INDEX idx_ref_id ON planetarySystem(ref_id); ANALYZE TABLE planetarySystem; ``` ``` tidb>CREATE INDEX idx_ref_id ON planetarySystem(ref_id); Query OK, 0 rows affected (2.87 sec) tidb>ANALYZE TABLE planetarySystem; Query OK, 0 rows affected, 1 warning (3.26 sec) ``` 16. Execute the following statement to view the EXPLAIN output for a query against the `planetarySystem` table. Enter the following statement at the TiDB prompt and receive the results shown: ```sql EXPLAIN SELECT id, pl_name, hostname FROM planetarySystem WHERE id=835868090839969012 OR ref_id=2048; ``` ``` tidb>EXPLAIN SELECT id, pl_name, hostname FROM planetarySystem WHERE id=835868090839969012 OR ref_id=2048; +----------------------------------+---------+-----------+-------------------------------------------------+--------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +----------------------------------+---------+-----------+-------------------------------------------------+--------------------------------------------------------------------------------------------------+ | Projection_4 | 17.00 | root | | universe.planetarysystem.id, universe.planetarysystem.pl_name, universe.planetarysystem.hostname | | └─IndexMerge_11 | 17.00 | root | | type: union | | ├─TableRangeScan_8(Build) | 1.00 | cop[tikv] | table:planetarySystem | range:[835868090839969012,835868090839969012], keep order:false | | ├─IndexRangeScan_9(Build) | 16.00 | cop[tikv] | table:planetarySystem, index:idx_ref_id(ref_id) | range:[2048,2048], keep order:false | | └─TableRowIDScan_10(Probe) | 17.00 | cop[tikv] | table:planetarySystem | keep order:false | +----------------------------------+---------+-----------+-------------------------------------------------+--------------------------------------------------------------------------------------------------+ 5 rows in set (0.00 sec) ``` + What is the access method for this query? Answer: index merge. + Does this access strategy result in good performance? Why? Answer: Maybe. The index merge access strategy tells you that TiDB has found multiple indexes that it can use for the query. In this case, it uses the primary key and the `ref_id` index. The index merge method retrieves rows with multiple range scans and merges the results Performance is good if there are high selectivity indexes on the table. The `operator info` Using union entry shows how TiDB merges index scans for the index merge join type. + Selectivity is a ratio between the number of values in a column and the number of values that are distinct or unique. High selectivity means that the ratio is approaching 1:1. 17. Drop the `idx_ref_id` index from the `planetarySystem` table. ```sql DROP INDEX idx_ref_id ON planetarySystem; ``` ``` tidb>DROP INDEX idx_ref_id ON planetarySystem; Query OK, 0 rows affected (0.36 sec) ``` 18. Leave the terminal window with the TiDB session open for the next exercise. ## Exercise 2: Improving the Performance of a Query ### Overview + In this practice, you improve the performance of a specific query. To accomplish this, you perform the following tasks: + Examine the query execution plan by using the EXPLAIN command. + Determine what changes to make to improve the response time + Implement the changes ### Duration + This exercise should take you approximately 15 minutes to complete. ### Tasks 1. In the TiDB session from the previous practice, execute the following statement to view the EXPLAIN output for a query against the `planetarySystem` table in the `universe` database: ```sql EXPLAIN SELECT pl_name, hostname, disc_year FROM planetarySystem WHERE hostname='14 Her' ORDER BY disc_year DESC\G; ``` + What is the access method for this query? + Which indexes can the optimizer use for this query? + What information does the `operator info` column contain? + Does this query perform well? Why? + What change must you make to the table to prevent the query from performing a full table scan? 2. Improve the performance of the query by making the change identified in the previous step. 3. Re-issue the EXPLAIN statement from step 1. + What is the access method for this query? + What key does the optimizer use? + What information does the `operator info` column contain? + Does the change you implemented in the preceding step improve the performance of the query? Why? + What must you do to prevent this query from having to perform a Sort? 4. Remove the necessity for a Sort operation by making the changes identified in the previous step. 5. Reissue the EXPLAIN statement from step 1. + What key does the optimizer use? + What information does the `operator info` column contain? + Does this change improve the performance of the query? Why? 6. What must you do to prevent this query from requiring a primary key lookup after finding the indexed row? 7. Make the necessary change identified in the previous step and remove any indexes that the change makes redundant. 8. Re-issue the EXPLAIN statement from step 1. + What key does the optimizer use? + What information does the `operator info` column contain? + Does this change improve the performance of the query? Why? 9. Drop any indexes that you added in this practice to return the `planetarySystem` table to its original state. 10. Exit the TiDB session but leave the Linux terminal window open for the next exercise. ### Solution 1. In the TiDB session from the previous practice, execute the following statement to view the EXPLAIN output for a query against the `planetarySystem` table in the `universe` database: ```sql EXPLAIN SELECT pl_name, hostname, disc_year FROM planetarySystem WHERE hostname='14 Her' ORDER BY disc_year DESC; ``` Enter the following statement at the TiDB prompt and receive the results shown: ``` tidb>EXPLAIN SELECT pl_name, hostname, disc_year FROM planetarySystem WHERE hostname='14 Her' ORDER BY disc_year DESC; +---------------------------+----------+-----------+-----------------------+-------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------------+----------+-----------+-----------------------+-------------------------------------------------+ | Sort_5 | 5.81 | root | | universe.planetarysystem.disc_year:desc | | └─TableReader_10 | 5.81 | root | | data:Selection_9 | | └─Selection_9 | 5.81 | cop[tikv] | | eq(universe.planetarysystem.hostname, "14 Her") | | └─TableFullScan_8 | 34713.00 | cop[tikv] | table:planetarySystem | keep order:false | +---------------------------+----------+-----------+-----------------------+-------------------------------------------------+ 4 rows in set (0.00 sec) ``` + What is the access method for this query? Answer: TableFullScan, Selection, TableReader and Sort. + Which indexes can the optimizer use for this query? Answer: None + What information does the `operator info` column contain? Answer: + eq(universe.planetarysystem.hostname, "14 Her"): The query uses a WHERE clause to filter the output rows. + universe.planetarysystem.disc_year:desc: TiDB must perform an extra pass through the table to work out how to retrieve the rows in sorted order. The sort is performed by iterating through all the rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause. TiDB Sewrver sorts the keys, and then retrieves the rows in the required order. + Does this query perform well? Why? Answer: No. This query requires a full table scan(type: TableFullScan), cannot use any keys, and requires a sort on the `disc_year` column. Sort requires all data to be gathered in one TiDB Server for sorting after being read from the TiKV nodes. + What change must you make to the table to prevent the query from performing a full table scan? Answer: You must add an index to the `hostname` column of the `planetarySystem` table. 2. Improve the performance of the query by making the change identified in the previous step. Enter the following statement at the TiDB prompt and receive the results shown: ```sql ALTER TABLE planetarySystem ADD INDEX idx_hostname(hostname); ANALYZE TABLE planetarySystem; ``` ``` tidb>ALTER TABLE planetarySystem ADD INDEX idx_hostname(hostname); Query OK, 0 rows affected (3.08 sec) tidb>ANALYZE TABLE planetarySystem; Query OK, 0 rows affected, 1 warning (3.41 sec) ``` 3. Re-issue the EXPLAIN statement from step 1. Enter the following statement at the TiDB prompt and receive the results shown: ```sql EXPLAIN SELECT pl_name, hostname, disc_year FROM planetarySystem WHERE hostname='14 Her' ORDER BY disc_year DESC; ``` ``` tidb>EXPLAIN SELECT pl_name, hostname, disc_year FROM planetarySystem WHERE hostname='14 Her' ORDER BY disc_year DESC; +----------------------------------+---------+-----------+-----------------------------------------------------+---------------------------------------------+ | id | estRows | task | access object | operator info | +----------------------------------+---------+-----------+-----------------------------------------------------+---------------------------------------------+ | Sort_5 | 5.81 | root | | universe.planetarysystem.disc_year:desc | | └─IndexLookUp_13 | 5.81 | root | | | | ├─IndexRangeScan_11(Build) | 5.81 | cop[tikv] | table:planetarySystem, index:idx_hostname(hostname) | range:["14 Her","14 Her"], keep order:false | | └─TableRowIDScan_12(Probe) | 5.81 | cop[tikv] | table:planetarySystem | keep order:false | +----------------------------------+---------+-----------+-----------------------------------------------------+---------------------------------------------+ 4 rows in set (0.00 sec) ``` + What is the access method for this query? Answer: IndexRangeScan, IndexLookUp and Sort. + What key does the optimizer use? Answer: `hostname` + What information does the `operator info` column contain? Answer: range:["14 Her","14 Her"], keep order:false; universe.planetarysystem.disc_year:desc. + Does the change that you implemented in the preceding step improve the performance of the query? Why? Answer: Yes. Now the query does not require a full table scan. Instead, it uses the IndexRangeScan and IndexLookUp access methods to retrieve the rows from the index on the `hostname` column. In addition, task: cop[tikv] column shows all index filtering operations are completed in parallel in TiKVs. + What must you do to prevent this query from having to perform a Sort？Answer: Add a composite index to the `planetarySystem` table that includes the `hostname` and `disc_year` columns. The index on the `hostname` column is no longer required in this case, because TiDB can use the index prefix. 4. Remove the necessity for a Sort operation by making the changes identified in the previous step. Enter the following statement at the TiDB prompt and receive the results shown: ```sql ALTER TABLE planetarySystem ADD INDEX idx_hostname_disc_year(hostname, disc_year); ALTER TABLE planetarySystem DROP INDEX idx_hostname; ``` ``` tidb>ALTER TABLE planetarySystem ADD INDEX idx_hostname_disc_year(hostname, disc_year); Query OK, 0 rows affected (3.06 sec) tidb>ALTER TABLE planetarySystem DROP INDEX idx_hostname; Query OK, 0 rows affected (0.36 sec) ``` 5. Reissue the EXPLAIN statement from step 1. Enter the following statement at the TiDB prompt and receive the results shown: ```sql EXPLAIN SELECT pl_name, hostname, disc_year FROM planetarySystem WHERE hostname='14 Her' ORDER BY disc_year DESC; ``` ``` tidb>EXPLAIN SELECT pl_name, hostname, disc_year FROM planetarySystem WHERE hostname='14 Her' ORDER BY disc_year DESC; +----------------------------------+---------+-----------+--------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +----------------------------------+---------+-----------+--------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------+ | Projection_18 | 5.81 | root | | universe.planetarysystem.pl_name, universe.planetarysystem.hostname, universe.planetarysystem.disc_year | | └─IndexLookUp_17 | 5.81 | root | | | | ├─IndexRangeScan_15(Build) | 5.81 | cop[tikv] | table:planetarySystem, index:idx_hostname_disc_year(hostname, disc_year) | range:["14 Her","14 Her"], keep order:true, desc | | └─TableRowIDScan_16(Probe) | 5.81 | cop[tikv] | table:planetarySystem | keep order:false | +----------------------------------+---------+-----------+--------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) ``` + What key does the optimizer use? Answer: The key that you added in the preceding step. (`idx_hostname_disc_year` in the example solution) + What information does the `operator info` column contain? Answer: range:["14 Her","14 Her"], keep order:true, desc. + Does this change improve the performance of the query? Why? Answer: Yes. Adding a composite key on the `hostname` and `disc_year` columns removes the requirement to perform a Sort int TiDB Server. 6. What must you do to prevent this query from requiring a primary key lookup after finding the indexed row? Answer: Add a composite index to the `planetarySystem` table that includes the `hostname`, `disc_year`, and `pl_name` columns. Drop the composite index on the `hostname` and `disc_year` columns, because TiDB can use the index prefix. 7. Make the necessary change identified in the previous step and remove any indexes that the change makes redundant. Enter the following statements at the TiDB prompt and receive the results shown: ```sql ALTER TABLE planetarySystem ADD INDEX idx_hostname_disc_year_pl_name(hostname, disc_year, pl_name); ALTER TABLE planetarySystem DROP INDEX idx_hostname_disc_year; ``` ``` tidb>ALTER TABLE planetarySystem ADD INDEX idx_hostname_disc_year_pl_name(hostname, disc_year, pl_name); Query OK, 0 rows affected (3.09 sec) tidb>ALTER TABLE planetarySystem DROP INDEX idx_hostname_disc_year; Query OK, 0 rows affected (0.35 sec) ``` 8. Re-issue the EXPLAIN statement from step 1. Enter the following statement at the TiDB prompt and receive the results shown: ```sql EXPLAIN SELECT pl_name, hostname, disc_year FROM planetarySystem WHERE hostname='14 Her' ORDER BY disc_year DESC; ``` ``` tidb>EXPLAIN SELECT pl_name, hostname, disc_year FROM planetarySystem WHERE hostname='14 Her' ORDER BY disc_year DESC; +-------------------------+---------+-----------+-------------------------------------------------------------------------------------------+--------------------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------+---------+-----------+-------------------------------------------------------------------------------------------+--------------------------------------------------+ | IndexReader_12 | 5.81 | root | | index:IndexRangeScan_11 | | └─IndexRangeScan_11 | 5.81 | cop[tikv] | table:planetarySystem, index:idx_hostname_disc_year_pl_name(hostname, disc_year, pl_name) | range:["14 Her","14 Her"], keep order:true, desc | +-------------------------+---------+-----------+-------------------------------------------------------------------------------------------+--------------------------------------------------+ 2 rows in set (0.00 sec) ``` + What key does the optimizer use? Answer: idx_hostname_disc_year_pl_name + What information does the `operator info` column contain? Answer: range:["14 Her","14 Her"], keep order:true, desc; index:IndexRangeScan_11 + Does this change improve the performance of the query? Why? Answer: Yes. The query now retrieves all the data that it needs from the index, without accessing the base table (`operator info`: index:IndexRangeScan_11). This type of index is known as a covering index. Performance will be very good for reads, but there might be a negative impact on performance for table updates because TiDB must update the index too. 9. Drop any indexes that you added in this practice to return the `planetarySystem` table to its original state. Enter the following statement at the TiDB prompt and receive the results shown: ```sql ALTER TABLE planetarySystem DROP INDEX idx_hostname_disc_year_pl_name; ``` ``` tidb>ALTER TABLE planetarySystem DROP INDEX idx_hostname_disc_year_pl_name; Query OK, 0 rows affected (0.35 sec) ``` 10. Exit the TiDB session but leave the Linux terminal window open for the next exercise. Enter the following statement at the TiDB prompt and receive the results shown: ``` tidb>EXIT Bye ``` ## Exercise 3: Table Join for Query Performance ### Overview + In this exercise, you examine the TiDB optimizer's execution plan for several different table join queries by using the EXPLAIN command. To accomplish this, you perform the following tasks: + Determine the optimizer's strategy for a particular query. + Add indexes to the tables that the query accesses and evaluate the effect on the query execution plan. ### Duration + This exercise should take you approximately 15 minutes to complete. ### Tasks 1. Open a Linux terminal window and start an interactive TiDB session. 2. Make `universe` the current database. 3. Execute the following statement to view the EXPLAIN output for a query against the `planetarySystem` table: ```sql EXPLAIN SELECT A.pl_name, A.hostname, A.discoverymethod, A.disc_year,B.name as DiscoveryFacility, C.ref_name as ParameterReference FROM planetarySystem A, discoveryFacility B, parameterReference C WHERE A.df_id = B.id AND A.ref_id = C.id AND C.ref_name LIKE 'Rosenthal%' AND A.hostname LIKE '14 Her' AND A.disc_year > 2020\G; ``` + Which two tables are joined first? What type of table join is used? + Which table only participates in the second table join? What type of table join is used? + What is the access method for each table query? + Does this access strategy result in good performance? Why? 4. Make the necessary change identified in the previous step. 5. Re-issue the EXPLAIN statement from step 3. Explain the difference in the output. + Explain the join type for the first table in the join. + Which two tables are joined first? What type of table join is used? + Which table only participates in the second table join? What type of table join is used? + What is the access method for each table query? + Does this access strategy result in good performance? Why? 6. Add index `idx_ref_id` on `ref_id` column of table `planetarySystem` and add index `idx_df_id` on `df_id` column of table `planetarySystem`. 7. Re-issue the EXPLAIN statement from step 5. Explain the difference in the output. + Has the join order of three tables been changed? Why? 8. Drop any indexes that you added in this practice to return the `planetarySystem`, `discoveryFacility` and `parameterReference` table to its original state. 9. Execute the following statement to view the EXPLAIN output for a query against the `planetarySystem` table: ```sql EXPLAIN SELECT * FROM planetarySystem WHERE ref_id IN ( SELECT ID FROM parameterReference WHERE ref_name LIKE 'Rosenthal%'); ``` + What is the join type in the join? + What is the select type for the tables in the join? + Does this access strategy result in good performance? Why? 10. Make the necessary change identified in the previous step. 11. Re-issue the EXPLAIN statement from step 9. Explain the difference in the output. + What is the join type in the join? + What is the select type for the tables in the join? + Does this access strategy result in good performance? Why? 11. Drop any indexes that you added in this practice to return the `planetarySystem` and `parameterReference` table to its original state. 12. Exit the TiDB session but leave the Linux terminal window open for the next exercise. ### Solution 1. 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: ``` $ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root -p mysql> prompt tidb> PROMPT set to 'tidb>' ``` 2. Make `universe` the current database. Enter the following statement at the TiDB prompt and receive the results shown: ```sql use universe; ``` ``` tidb>use universe; Database changed ``` 3. Execute the following statement to view the EXPLAIN output for a query against the `planetarySystem` table. Enter the following statement at the TiDB prompt and receive the results shown: ```sql EXPLAIN SELECT A.pl_name, A.hostname, A.discoverymethod, A.disc_year,B.name as DiscoveryFacility, C.ref_name as ParameterReference FROM planetarySystem A, discoveryFacility B, parameterReference C WHERE A.df_id = B.id AND A.ref_id = C.id AND C.ref_name LIKE 'Rosenthal%' AND A.hostname LIKE '14 Her' AND A.disc_year > 2020; ``` ``` tidb>EXPLAIN SELECT A.pl_name, A.hostname, A.discoverymethod, A.disc_year,B.name as DiscoveryFacility, C.ref_name as ParameterReference -> FROM planetarySystem A, discoveryFacility B, parameterReference C -> WHERE A.df_id = B.id AND A.ref_id = C.id AND C.ref_name LIKE 'Rosenthal%' AND A.hostname LIKE '14 Her' AND A.disc_year > 2020; +------------------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +------------------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Projection_13 | 1.98 | root | | universe.planetarysystem.pl_name, universe.planetarysystem.hostname, universe.planetarysystem.discoverymethod, universe.planetarysystem.disc_year, universe.discoveryfacility.name, universe.parameterreference.ref_name | | └─IndexJoin_18 | 1.98 | root | | inner join, inner:TableReader_15, outer key:universe.planetarysystem.df_id, inner key:universe.discoveryfacility.id, equal cond:eq(universe.planetarysystem.df_id, universe.discoveryfacility.id) | | ├─IndexJoin_31(Build) | 1.98 | root | | inner join, inner:TableReader_27, outer key:universe.planetarysystem.ref_id, inner key:universe.parameterreference.id, equal cond:eq(universe.planetarysystem.ref_id, universe.parameterreference.id) | | │ ├─TableReader_40(Build) | 1.98 | root | | data:Selection_39 | | │ │ └─Selection_39 | 1.98 | cop[tikv] | | gt(universe.planetarysystem.disc_year, 2020), like(universe.planetarysystem.hostname, "14 Her", 92) | | │ │ └─TableFullScan_38 | 34713.00 | cop[tikv] | table:A | keep order:false | | │ └─TableReader_27(Probe) | 0.00 | root | | data:Selection_26 | | │ └─Selection_26 | 0.00 | cop[tikv] | | like(universe.parameterreference.ref_name, "Rosenthal%", 92) | | │ └─TableRangeScan_25 | 1.98 | cop[tikv] | table:C | range: decided by [universe.planetarysystem.ref_id], keep order:false | | └─TableReader_15(Probe) | 1.98 | root | | data:TableRangeScan_14 | | └─TableRangeScan_14 | 1.98 | cop[tikv] | table:B | range: decided by [universe.planetarysystem.df_id], keep order:false | +------------------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 11 rows in set (0.01 sec) ``` + Which two tables are joined first? What type of table join is used? Answer: Table planetarySystem (A) and parameterReference (C) are joined first, the join type is IndexJoin. + Which table only participates in the second table join? What type of table join is used? Answer: Table discoveryFacility (B), the join type is IndexJoin. + What is the access method for each table query? Anserver: TableFullScan, Selection and TableReader for table planetarySystem (A); TableRangeScan, Selection and TableReader for table parameterReference (C); TableRangeScan and TableReader for table discoveryFacility (B). + Does this access strategy result in good performance? Why? No. This is because in the `WHERE` clause, you see filter conditions like `C.ref_name LIKE 'Rosenthal%' AND A.hostname LIKE '14 Her' AND A.disc_year > 2020`. We can create indexes to accelerate these filter operations in TiKV nodes and avoid TableFullScan or other filtering actions such as SELECT, in order to reduce IO pressure. 4. Make the necessary change identified in the previous step. Enter the following statement at the TiDB prompt and receive the results shown: ```sql CREATE INDEX idx_hostname_disc_year ON planetarySystem(hostname, disc_year); CREATE INDEX idx_ref_name ON parameterReference(ref_name); ANALYZE TABLE planetarySystem; ANALYZE TABLE parameterReference; ``` ``` tidb>CREATE INDEX idx_hostname_disc_year ON planetarySystem(hostname, disc_year); Query OK, 0 rows affected (6.03 sec) tidb>CREATE INDEX idx_ref_name ON ParameterReference(ref_name); Query OK, 0 rows affected (3.02 sec) tidb>ANALYZE TABLE planetarySystem; Query OK, 0 rows affected, 1 warning (3.14 sec) tidb>ANALYZE TABLE parameterReference; Query OK, 0 rows affected, 1 warning (0.63 sec) ``` 5. Re-issue the EXPLAIN statement from step 3. Explain the difference in the output.Enter the following statement at the TiDB prompt and receive the results shown: ```sql EXPLAIN SELECT A.pl_name, A.hostname, A.discoverymethod, A.disc_year,B.name as DiscoveryFacility, C.ref_name as ParameterReference FROM planetarySystem A, discoveryFacility B, parameterReference C WHERE A.df_id = B.id AND A.ref_id = C.id AND C.ref_name LIKE 'Rosenthal%' AND A.hostname LIKE '14 Her' AND A.disc_year > 2020; ``` ``` tidb>EXPLAIN SELECT A.pl_name, A.hostname, A.discoverymethod, A.disc_year,B.name as DiscoveryFacility, C.ref_name as ParameterReference -> FROM planetarySystem A, discoveryFacility B, parameterReference C -> WHERE A.df_id = B.id AND A.ref_id = C.id AND C.ref_name LIKE 'Rosenthal%' AND A.hostname LIKE '14 Her' AND A.disc_year > 2020; +------------------------------------------+---------+-----------+------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +------------------------------------------+---------+-----------+------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Projection_13 | 1.98 | root | | universe.planetarysystem.pl_name, universe.planetarysystem.hostname, universe.planetarysystem.discoverymethod, universe.planetarysystem.disc_year, universe.discoveryfacility.name, universe.parameterreference.ref_name | | └─IndexJoin_18 | 1.98 | root | | inner join, inner:TableReader_15, outer key:universe.planetarysystem.df_id, inner key:universe.discoveryfacility.id, equal cond:eq(universe.planetarysystem.df_id, universe.discoveryfacility.id) | | ├─HashJoin_37(Build) | 1.98 | root | | inner join, equal:[eq(universe.planetarysystem.ref_id, universe.parameterreference.id)] | | │ ├─IndexLookUp_43(Build) | 1.98 | root | | | | │ │ ├─IndexRangeScan_41(Build) | 1.98 | cop[tikv] | table:A, index:idx_hostname_disc_year(hostname, disc_year) | range:("14 Her" 2020,"14 Her" +inf], keep order:false | | │ │ └─TableRowIDScan_42(Probe) | 1.98 | cop[tikv] | table:A | keep order:false | | │ └─IndexReader_45(Probe) | 2.72 | root | | index:IndexRangeScan_44 | | │ └─IndexRangeScan_44 | 2.72 | cop[tikv] | table:C, index:idx_ref_name(ref_name) | range:["Rosenthal","Rosentham"), keep order:false | | └─TableReader_15(Probe) | 1.98 | root | | data:TableRangeScan_14 | | └─TableRangeScan_14 | 1.98 | cop[tikv] | table:B | range: decided by [universe.planetarysystem.df_id], keep order:false | +------------------------------------------+---------+-----------+------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 10 rows in set (0.01 sec) ``` + Which two tables are joined first? What type of table join is used? Answer: Table planetarySystem (A) and parameterReference (C) are joined first, the join type is HashJoin. + Which table only participates in the second table join? What type of table join is used? Answer: Table discoveryFacility (B), the join type is IndexJoin. + What is the access method for each table query? Anserver: IndexRangeScan and IndexLookUp for table planetarySystem (A); IndexRangeScan and IndexReader for table parameterReference (C); TableRangeScan and TableReader for table discoveryFacility (B). + Does this access strategy result in good performance? Why? Yes. This is because TableFullScan or other filtering actions such as SELECT before table joining are avoided by using indexes. 6. Add index `idx_ref_id` on `ref_id` column of table `planetarySystem` and add index `idx_df_id` on `df_id` column of table `planetarySystem`. Enter the following statement at the TiDB prompt and receive the results shown: ```sql CREATE INDEX idx_ref_id ON planetarySystem(ref_id); CREATE INDEX idx_df_id ON planetarySystem(df_id); ANALYZE TABLE planetarySystem; ``` ``` tidb>CREATE INDEX idx_ref_id ON planetarySystem(ref_id); Query OK, 0 rows affected (6.03 sec) tidb>CREATE INDEX idx_df_id ON planetarySystem(df_id); Query OK, 0 rows affected (6.03 sec) tidb>ANALYZE TABLE planetarySystem; Query OK, 0 rows affected, 1 warning (4.00 sec) ``` 7. Re-issue the EXPLAIN statement from step 5. Explain the difference in the output. Enter the following statement at the TiDB prompt and receive the results shown: ```sql EXPLAIN SELECT A.pl_name, A.hostname, A.discoverymethod, A.disc_year,B.name as DiscoveryFacility, C.ref_name as ParameterReference FROM planetarySystem A, discoveryFacility B, parameterReference C WHERE A.df_id = B.id AND A.ref_id = C.id AND C.ref_name LIKE 'Rosenthal%' AND A.hostname LIKE '14 Her' AND A.disc_year > 2020; ``` ``` tidb>EXPLAIN SELECT A.pl_name, A.hostname, A.discoverymethod, A.disc_year,B.name as DiscoveryFacility, C.ref_name as ParameterReference -> FROM planetarySystem A, discoveryFacility B, parameterReference C -> WHERE A.df_id = B.id AND A.ref_id = C.id AND C.ref_name LIKE 'Rosenthal%' AND A.hostname LIKE '14 Her' AND A.disc_year > 2020; +------------------------------------------+---------+-----------+------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +------------------------------------------+---------+-----------+------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Projection_13 | 1.98 | root | | universe.planetarysystem.pl_name, universe.planetarysystem.hostname, universe.planetarysystem.discoverymethod, universe.planetarysystem.disc_year, universe.discoveryfacility.name, universe.parameterreference.ref_name | | └─IndexJoin_19 | 1.98 | root | | inner join, inner:TableReader_16, outer key:universe.planetarysystem.df_id, inner key:universe.discoveryfacility.id, equal cond:eq(universe.planetarysystem.df_id, universe.discoveryfacility.id) | | ├─HashJoin_71(Build) | 1.98 | root | | inner join, equal:[eq(universe.planetarysystem.ref_id, universe.parameterreference.id)] | | │ ├─IndexLookUp_85(Build) | 1.98 | root | | | | │ │ ├─IndexRangeScan_83(Build) | 1.98 | cop[tikv] | table:A, index:idx_hostname_disc_year(hostname, disc_year) | range:("14 Her" 2020,"14 Her" +inf], keep order:false | | │ │ └─TableRowIDScan_84(Probe) | 1.98 | cop[tikv] | table:A | keep order:false | | │ └─IndexReader_87(Probe) | 2.72 | root | | index:IndexRangeScan_86 | | │ └─IndexRangeScan_86 | 2.72 | cop[tikv] | table:C, index:idx_ref_name(ref_name) | range:["Rosenthal","Rosentham"), keep order:false | | └─TableReader_16(Probe) | 1.98 | root | | data:TableRangeScan_15 | | └─TableRangeScan_15 | 1.98 | cop[tikv] | table:B | range: decided by [universe.planetarysystem.df_id], keep order:false | +------------------------------------------+---------+-----------+------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 10 rows in set (0.01 sec) ``` + Has the join order of three tables been changed? Why? Answer: No. Because the optimizer often determines the order of the join based on the amount of data involved in each table, with the table having the smallest amount of data being the first to join. It determine the number of rows in each table that participate in the join based on the filtering conditions in the query, as follows: ``` tidb>SELECT COUNT(*) FROM planetarysystem WHERE hostname LIKE '14 Her' AND disc_year > 2020; +----------+ | COUNT(*) | +----------+ | 2 | +----------+ 1 row in set (0.01 sec) tidb>SELECT COUNT(*) FROM parameterReference WHERE ref_name LIKE 'Rosenthal%'; +----------+ | COUNT(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec) tidb>SELECT COUNT(*) FROM discoveryFacility; +----------+ | COUNT(*) | +----------+ | 70 | +----------+ 1 row in set (0.00 sec) ``` The conclusion is that the `planetarySystem` and `parameterReference` tables participates in the join with the fewest rows, while the `discoveryFacility` table participates in the connection with the most rows." 8. Drop any indexes that you added in this practice to return the `planetarySystem`, `discoveryFacility` and `parameterReference` table to its original state. Enter the following statement at the TiDB prompt and receive the results shown: ```sql ALTER TABLE planetarySystem DROP INDEX idx_ref_id; ALTER TABLE planetarySystem DROP INDEX idx_df_id; ALTER TABLE planetarySystem DROP INDEX idx_hostname_disc_year; ALTER TABLE ParameterReference DROP INDEX idx_ref_name; ``` ``` tidb>ALTER TABLE planetarySystem DROP INDEX idx_ref_id; Query OK, 0 rows affected (0.53 sec) tidb>ALTER TABLE planetarySystem DROP INDEX idx_df_id; Query OK, 0 rows affected (0.53 sec) tidb>ALTER TABLE planetarySystem DROP INDEX idx_hostname_disc_year; Query OK, 0 rows affected (0.53 sec) tidb>ALTER TABLE ParameterReference DROP INDEX idx_ref_name; Query OK, 0 rows affected (0.52 sec) ``` 9. Execute the following statement to view the EXPLAIN output for a query against the `planetarySystem` table. Enter the following statement at the TiDB prompt and receive the results shown: ```sql EXPLAIN SELECT * FROM planetarySystem WHERE ref_id IN ( SELECT ID FROM parameterReference WHERE ref_name LIKE 'Rosenthal%'); ``` ``` tidb>EXPLAIN SELECT * FROM planetarySystem WHERE ref_id IN ( SELECT ID FROM parameterReference WHERE ref_name LIKE 'Rosenthal%'); +------------------------------+----------+-----------+--------------------------+-----------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +------------------------------+----------+-----------+--------------------------+-----------------------------------------------------------------------------------------+ | HashJoin_26 | 45.97 | root | | inner join, equal:[eq(universe.parameterreference.id, universe.planetarysystem.ref_id)] | | ├─TableReader_31(Build) | 2.72 | root | | data:Selection_30 | | │ └─Selection_30 | 2.72 | cop[tikv] | | like(universe.parameterreference.ref_name, "Rosenthal%", 92) | | │ └─TableFullScan_29 | 2055.00 | cop[tikv] | table:parameterReference | keep order:false | | └─TableReader_28(Probe) | 34713.00 | root | | data:TableFullScan_27 | | └─TableFullScan_27 | 34713.00 | cop[tikv] | table:planetarySystem | keep order:false | +------------------------------+----------+-----------+--------------------------+-----------------------------------------------------------------------------------------+ 6 rows in set (0.00 sec) ``` + What is the join type in the join? Answer: HashJoin + What is the select type for the tables in the join? Answer: TableFullScan, Selection and TableReader for table `parameterReference`; TableFullScan and TableReader for table `parameterReference`. + Does this access strategy result in good performance? Why? No. First of all, for filtering with the WHERE clause, we can create indexes of table `parameterReference`. Secondly, we can also create indexes on the join columns of table `planetarySystem`. 10. Make the necessary change identified in the previous step. Enter the following statement at the TiDB prompt and receive the results shown: ```sql CREATE INDEX idx_ref_id ON planetarySystem(ref_id); CREATE INDEX idx_ref_name ON ParameterReference(ref_name); ANALYZE TABLE planetarySystem; ANALYZE TABLE ParameterReference; ``` ``` tidb>CREATE INDEX idx_ref_id ON planetarySystem(ref_id); Query OK, 0 rows affected (6.03 sec) tidb>CREATE INDEX idx_ref_name ON ParameterReference(ref_name); Query OK, 0 rows affected (3.03 sec) tidb>ANALYZE TABLE planetarySystem; Query OK, 0 rows affected, 1 warning (3.79 sec) tidb>ANALYZE TABLE ParameterReference; Query OK, 0 rows affected, 1 warning (0.83 sec) ``` 11. Re-issue the EXPLAIN statement from step 9. Explain the difference in the output. Enter the following statement at the TiDB prompt and receive the results shown: ```sql EXPLAIN SELECT * FROM planetarySystem WHERE ref_id IN ( SELECT ID FROM parameterReference WHERE ref_name LIKE 'Rosenthal%'); ``` ``` tidb>EXPLAIN SELECT * FROM planetarySystem WHERE ref_id IN ( SELECT ID FROM parameterReference WHERE ref_name LIKE 'Rosenthal%'); +----------------------------------+---------+-----------+--------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +----------------------------------+---------+-----------+--------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | IndexHashJoin_20 | 45.97 | root | | inner join, inner:IndexLookUp_17, outer key:universe.parameterreference.id, inner key:universe.planetarysystem.ref_id, equal cond:eq(universe.parameterreference.id, universe.planetarysystem.ref_id) | | ├─IndexReader_46(Build) | 2.72 | root | | index:IndexRangeScan_45 | | │ └─IndexRangeScan_45 | 2.72 | cop[tikv] | table:parameterReference, index:idx_ref_name(ref_name) | range:["Rosenthal","Rosentham"), keep order:false | | └─IndexLookUp_17(Probe) | 45.97 | root | | | | ├─IndexRangeScan_15(Build) | 45.97 | cop[tikv] | table:planetarySystem, index:idx_ref_id(ref_id) | range: decided by [eq(universe.planetarysystem.ref_id, universe.parameterreference.id)], keep order:false | | └─TableRowIDScan_16(Probe) | 45.97 | cop[tikv] | table:planetarySystem | keep order:false | +----------------------------------+---------+-----------+--------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 6 rows in set (0.00 sec) ``` + What is the join type in the join? Answer: IndexHashJoin + What is the select type for the tables in the join? Answer: IndexRangeScan and IndexReader for table `parameterReference`; IndexRangeScan and IndexLookUp for table `planetarySystem`. + Does this access strategy result in good performance? Why? Yes. The index `idx_ref_id` ensure that there is an index on the join columns of the `planetarySystem` table, the HashJoin can be turned into an IndexHashJoin, which avoids full table scans. At the same time, by ensuring that there is an index `idx_ref_name` on the `ParameterReference` table, filtering can be performed first to avoid full table scans. 11. Drop any indexes that you added in this practice to return the `planetarySystem` and `parameterReference` table to its original state. Enter the following statement at the TiDB prompt and receive the results shown: ```sql DROP INDEX idx_ref_id ON planetarysystem; DROP INDEX idx_ref_name ON parameterreference; ``` ``` tidb>DROP INDEX idx_ref_id ON planetarysystem; Query OK, 0 rows affected (0.52 sec) tidb>DROP INDEX idx_ref_name ON parameterreference; Query OK, 0 rows affected (0.52 sec) ``` 12. Exit the TiDB session but leave the Linux terminal window open for the next exercise. Enter the following statement at the TiDB prompt and receive the results shown: ``` tidb>EXIT Bye ``` ## End of Exercises