# Lab 4: Optimizing Queries ### Overview + In these exercises, 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 + Click [pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon) to download the private key file, which is required to log into the Control Machine for this lab. + Commands and SQL statements presented on this page are clickable for easy copy and paste. + Your environment may be different from the one shown on the lab guide, including but not limited to IP, port, username, password and directory. Example output is displayed in a gray background in this guide. + The `$` in your terminal window is an OS prompt. The `tidb>` or `mysql>` in your terminal window is SQL command prompt. ### Assumptions + The private key file is downloaded and placed under `~/.ssh/` folder locally. Relevant permission has been provided by [chmod 400 ~/.ssh/pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon) + For Windows users, please refer to this [guide](https://github.com/pingcap-inc/tidb-course-labs/blob/master/README_303_class_setup_guide_for_student_lab_control_en.md) for the private key preparation. ## Exercise 1 ### Subject: Data Preparation ### Overview + In this exercise, you will import more experimental data into TiDB database for the subsequent experiments. We will create a database called universe. ### Duration + This exercise should take you approximately 5 minutes to complete. ### Tasks + Connect to TiDB session. + Execute the script `universeV2.sql`. + Make `universe` the current database. + Check the imported data. ### Solution 0. Login as user ec2-user on monitor1_address. If you do not possess the credentials to connect, kindly review the Assumptions section under the OVERVIEW tab. The returned message may be slightly different from what is displayed here. If prompted, answer `yes`. ```command $ ssh -i ~/.ssh/pe-class-key-oregon.pem ec2-user@monitor1_address ``` ```output , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` 1. Go to the universe directory, connect to TiDB, and use `test` database. ```command cd universe/ ``` ```command $ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```command use test; ``` ```output Database changed ``` 2. Execute the script `universeV2.sql`. Enter the following command at the terminal prompt and receive the results shown: ```command source universeV2.sql ``` ```output tidb> source universeV2.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.54 sec) Database changed Query OK, 0 rows affected (0.53 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) ... ... Query OK, 0 rows affected, 1 warning (0.51 sec) Query OK, 0 rows affected, 1 warning (2.03 sec) ``` 3. Make `universe` the current database. ```command use universe; ``` ```output tidb>use universe; Database changed ``` 4. Check the imported data. ```command SHOW TABLES; SELECT COUNT(*) FROM stars; SELECT COUNT(*) FROM planets; SELECT COUNT(*) FROM moons; SELECT COUNT(*) FROM planet_categories; SELECT COUNT(*) FROM discoveryFacility; SELECT COUNT(*) FROM parameterReference; SELECT COUNT(*) FROM planetarySystem; ``` ```output tidb>show tables; +--------------------+ | Tables_in_universe | +--------------------+ | discoveryFacility | | moons | | parameterReference | | planet_categories | | planetarySystem | | planets | | stars | +--------------------+ 7 rows in set (0.00 sec) tidb>select count(*) from stars; +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec) tidb>select count(*) from planets; +----------+ | count(*) | +----------+ | 10 | +----------+ 1 row in set (0.00 sec) tidb>select count(*) from moons; +----------+ | count(*) | +----------+ | 28 | +----------+ 1 row in set (0.00 sec) tidb>select count(*) from planet_categories; +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set (0.00 sec) tidb>select count(*) from discoveryFacility; +----------+ | count(*) | +----------+ | 70 | +----------+ 1 row in set (0.00 sec) tidb>select count(*) from parameterReference; +----------+ | count(*) | +----------+ | 2055 | +----------+ 1 row in set (0.00 sec) tidb>select count(*) from planetarySystem; +----------+ | count(*) | +----------+ | 34713 | +----------+ 1 row in set (0.02 sec) ``` ## Exercise 2 ### Subject: Indexing Strategies ### Overview + In this exercise, you will import more experimental data into TiDB database for the subsequent experiments. We will create a database called universe. ### Duration + This exercise should take you approximately 20 minutes to complete. ### Tasks + Optimize and compare the execution plan for the following queries: ```command SELECT pl_name, hostname FROM planetarySystem WHERE pl_name='HAT-P-42 b' and hostname='HAT-P-42'; ``` ```command SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where hostname='11 Com' AND ref_id=2048; ``` ```command SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where pl_name='11 Com b' AND ref_id=2048; ``` ```command SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where pl_name='11 Com b' AND hostname='11 Com' AND ref_id=2048; ``` ```command SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where pl_name='11 Com b' AND hostname like '11%' AND ref_id=2048; ``` ```command SELECT id, pl_name, hostname FROM planetarySystem WHERE hostname = '11 Com' AND ref_id=2048; ``` ```command SELECT id, pl_name, hostname FROM planetarySystem WHERE id=835868090839969012 OR ref_id=2048; ``` ### Solution 1. Execute the following statement to view the EXPLAIN output for a query against the `planetarySystem` table. Then, answer the following questions. Finally, drop the `idx_pl_name` index from the `planetarySystem` table. ```command 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'; ``` ```output 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. ```command DROP INDEX idx_pl_name ON planetarySystem; ``` ```output tidb>DROP INDEX idx_pl_name ON planetarySystem; Query OK, 0 rows affected (0.36 sec) ``` 2. 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. Then, re-run the EXPLAIN statement, and explain the difference in the output. Finally, drop the `idx_pl_name_hostname` index from the `planetarySystem` table. ```command CREATE INDEX idx_pl_name_hostname on planetarySystem(pl_name, hostname); ANALYZE TABLE planetarySystem; ``` ```output 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) ``` ```command EXPLAIN SELECT pl_name, hostname FROM planetarySystem WHERE pl_name='HAT-P-42 b' and hostname='HAT-P-42'; ``` ```output 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`. ```command DROP INDEX idx_pl_name_hostname ON planetarySystem; ``` ```output tidb>DROP INDEX idx_pl_name_hostname ON planetarySystem; Query OK, 0 rows affected (0.37 sec) ``` 3. Add an index `idx_pl_name_hostname_ref` index on the `pl_name`, `hostname` and `ref_id` columns of the `planetarySystem` table. ```command CREATE INDEX idx_pl_name_hostname_ref ON planetarySystem(pl_name, hostname, ref_id); ANALYZE TABLE planetarySystem; ``` ```output 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) ``` 4. Execute the following statement to view the EXPLAIN output for a query against the `planetarySystem` table. Answer the following questions. ```command EXPLAIN SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where hostname='11 Com' AND ref_id=2048; ``` ```output 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`. 5. Execute the following statement to view the EXPLAIN output for a query against the `planetarySystem` table. Answer the following questions. ```command EXPLAIN SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where pl_name='11 Com b' AND ref_id=2048; ``` ```output 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`, `hostname`, `sy_snum`, `sy_pnum`, and `ref_id`. + Is the index `idx_pl_name_hostname_ref` helpful for improving the performance of this query? Why? Answer: Yes. 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 third line of the EXPLAIN output, in the Selection section, that the condition related to `ref_id` is completed separately after the `IndexRangeScan`. 6. Execute the following statement to view the EXPLAIN output for a query against the `planetarySystem` table. Answer the following questions. ```command 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; ``` ```output 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? Answer: `pl_name`, `hostname`, `sy_snum`, `sy_pnum` and `ref_id` columns. + Is the index `idx_pl_name_hostname_ref` helpful for improving the performance of this query? Why? Answer: Yes. The columns `pl_name`, `hostname`, and `ref_id` in the `index idx_pl_name_hostname_ref` all appear in the query. 7. Execute the following statement to view the EXPLAIN output for a query against the `planetarySystem` table. Answer the following questions. ```command 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; ``` ```output 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? Answer: `pl_name`, `hostname`, `sy_snum`, `sy_pnum`, and `ref_id`. + 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 third line of the EXPLAIN output, in the Selection section, that the condition related to `ref_id` is completed separately after the IndexRangeScan. 8. Execute the following statement to view the EXPLAIN output for a query against the `planetarySystem` table. Answer the following questions. Finally, drop the `idx_pl_name_hostname_ref` index from the `planetarySystem` table. ```command EXPLAIN SELECT id, pl_name, hostname FROM planetarySystem WHERE hostname = '11 Com' AND ref_id=2048; ``` ```output 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? Answer: 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? Answer: 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. ```command DROP INDEX idx_pl_name_hostname_ref ON planetarySystem; ``` ```output tidb>DROP INDEX idx_pl_name_hostname_ref ON planetarySystem; Query OK, 0 rows affected (0.36 sec) ``` 9. Add index `idx_ref_id` on the `ref_id` columns of the `planetarySystem` table. Then, execute the query to view the EXPLAIN output for a query against the `planetarySystem` table. Answer the questions. Finally, drop the `idx_ref_id` index from the `planetarySystem` table. ```command CREATE INDEX idx_ref_id ON planetarySystem(ref_id); ANALYZE TABLE planetarySystem; ``` ```output 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) ``` ```command EXPLAIN SELECT id, pl_name, hostname FROM planetarySystem WHERE id=835868090839969012 OR ref_id=2048; ``` ```output 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: `IndexMerge`. + 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. ```command DROP INDEX idx_ref_id ON planetarySystem; ``` ```output tidb>DROP INDEX idx_ref_id ON planetarySystem; Query OK, 0 rows affected (0.36 sec) ``` ## Exercise 3 ### Subject: Indexing Strategies with ORDER BY ### Overview + In this practice, you improve the performance of a query. To accomplish this, you need to: + 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 + Optimize the following query: ```command EXPLAIN SELECT pl_name, hostname, disc_year FROM planetarySystem WHERE hostname='14 Her' ORDER BY disc_year DESC; ``` ### Solution 1. Execute the following statement to view the EXPLAIN output for a query against the `planetarySystem` table in the `universe` database. ```command EXPLAIN SELECT pl_name, hostname, disc_year FROM planetarySystem WHERE hostname='14 Her' ORDER BY disc_year DESC; ``` ```output 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, and storing the sort keys together with the pointers to the row for all rows that match the WHERE clause. TiDB Server 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 loaded into one TiDB Server to finish the tasks. + 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. ```command ALTER TABLE planetarySystem ADD INDEX idx_hostname(hostname); ANALYZE TABLE planetarySystem; ``` ```output 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-run the EXPLAIN statement from step 1. Enter the following statement at the TiDB prompt and receive the results shown: ```command EXPLAIN SELECT pl_name, hostname, disc_year FROM planetarySystem WHERE hostname='14 Her' ORDER BY disc_year DESC; ``` ```output 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` 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 sort operation by making the changes identified in the previous step. ```command ALTER TABLE planetarySystem ADD INDEX idx_hostname_disc_year(hostname, disc_year); ALTER TABLE planetarySystem DROP INDEX idx_hostname; ``` ```output 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. Re-run the EXPLAIN statement from step 1. ```command EXPLAIN SELECT pl_name, hostname, disc_year FROM planetarySystem WHERE hostname='14 Her' ORDER BY disc_year DESC; ``` ```output 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 in 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. ```command 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; ``` ```output 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-run the EXPLAIN statement from step 1. ```command EXPLAIN SELECT pl_name, hostname, disc_year FROM planetarySystem WHERE hostname='14 Her' ORDER BY disc_year DESC; ``` ```output 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. ```command ALTER TABLE planetarySystem DROP INDEX idx_hostname_disc_year_pl_name; ``` ```output tidb>ALTER TABLE planetarySystem DROP INDEX idx_hostname_disc_year_pl_name; Query OK, 0 rows affected (0.35 sec) ``` ## Exercise 4 ### Subject: Indexing Strategies with JOIN ### 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 need to: + 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 + Optimize the following two queries: ```command 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; ``` ```command SELECT * FROM planetarySystem WHERE ref_id IN ( SELECT ID FROM parameterReference WHERE ref_name LIKE 'Rosenthal%'); ``` ### Solution 1. Execute the following statement to view the EXPLAIN output for a query against the `planetarySystem` table: ```command 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; ``` ```output 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? Answer: `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? Answer: 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. 2. Make the necessary change identified in the previous step. ```command 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; ``` ```output 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) ``` 3. Re-run the EXPLAIN statement from step 1. Explain the difference in the output. ```command 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; ``` ```output 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? Answer: `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? Answer: Yes. This is because `TableFullScan` or other filtering actions such as SELECT before table joining are avoided by using indexes. 4. 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`. ```command CREATE INDEX idx_ref_id ON planetarySystem(ref_id); CREATE INDEX idx_df_id ON planetarySystem(df_id); ANALYZE TABLE planetarySystem; ``` ```output 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) ``` 5. Re-run the EXPLAIN statement from step 3. Explain the difference in the output. Has the join order of three tables been changed? Why? ```command 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; ``` ```output 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: ```command SELECT COUNT(*) FROM planetarysystem WHERE hostname LIKE '14 Her' AND disc_year > 2020; SELECT COUNT(*) FROM parameterReference WHERE ref_name LIKE 'Rosenthal%'; SELECT COUNT(*) FROM discoveryFacility; ``` ```output 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. 6. Drop any indexes that you added in this practice to return the `planetarySystem`, `discoveryFacility` and `parameterReference` table to its original state. ```command 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; ``` ```output 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) ``` 7. Execute the following statement to view the EXPLAIN output for a query against the `planetarySystem` table: ```command EXPLAIN SELECT * FROM planetarySystem WHERE ref_id IN ( SELECT ID FROM parameterReference WHERE ref_name LIKE 'Rosenthal%'); ``` ```output 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? Answer: 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`. 8. Make the necessary change identified in the previous step. Then, re-run the `EXPLAIN` statement from step 7. Explain the difference in the output. ```command CREATE INDEX idx_ref_id ON planetarySystem(ref_id); CREATE INDEX idx_ref_name ON ParameterReference(ref_name); ANALYZE TABLE planetarySystem; ANALYZE TABLE ParameterReference; ``` ```output 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) ``` ```command EXPLAIN SELECT * FROM planetarySystem WHERE ref_id IN ( SELECT ID FROM parameterReference WHERE ref_name LIKE 'Rosenthal%'); ``` ```output 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? Answer: 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. 9. Drop any indexes that you added in this practice to return the `planetarySystem` and `parameterReference` table to its original state. ```command DROP INDEX idx_ref_id ON planetarysystem; DROP INDEX idx_ref_name ON parameterreference; ``` ```output 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) ``` ## End of Exercises