--- title: "Optimizing Queries" version: "v2" status: "GA" description: "In this hands-on lab, 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." --- # Lab 4: Optimizing Queries ## Overview >[!NOTE] > This hands-on lab is also included in the [TiDB Certification Training: TiDB SQL Tuning](https://www.pingcap.com/education/course-tidb-sql-tuning/) course. In this lab, you 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. > [!TIP] > Once the lab environment is deployed, click **Next** on the bottom bar to begin **Module 1**. ## Module 1: Data Preparation + In this exercise, you will import more experimental data into TiDB database for the subsequent experiments. We will create a database called universe. ### Step 1. Log in to the remote Linux + Click here - [Terminal 1](ssh://monitor1-ssh#1) to open a new terminal connected to the virtual machine assigned for your hands-on lab. + The banner displayed after logging in appears as follows. ```output , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` ### Step 2. Connect to TiDB and use `test` database ```sql cd universe/ ``` ```sql mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```sql use test; ``` ```output Database changed ``` ### Step 3. Import the data ```sql source universeV2.sql ``` ```output Query OK, 0 rows affected, 1 warning (2.03 sec) ``` ### Step 4. Make `universe` the current database ```sql use universe; ``` ```output Database changed ``` ### Step 5. Check the imported data ```sql 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) ``` ## Module 2: Indexing Strategies + In this module, we optimize and compare the execution plan for the following queries: ```sql SELECT pl_name, hostname FROM planetarySystem WHERE pl_name='HAT-P-42 b' and hostname='HAT-P-42'; ``` ```sql SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where hostname='11 Com' AND ref_id=2048; ``` ```sql SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where pl_name='11 Com b' AND ref_id=2048; ``` ```sql SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where pl_name='11 Com b' AND hostname='11 Com' AND ref_id=2048; ``` ```sql SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where pl_name='11 Com b' AND hostname like '11%' AND ref_id=2048; ``` ```sql SELECT id, pl_name, hostname FROM planetarySystem WHERE hostname = '11 Com' AND ref_id=2048; ``` ```sql SELECT id, pl_name, hostname FROM planetarySystem WHERE id=835868090839969012 OR ref_id=2048; ``` ### Step 1. Add an index and check the execution plan for the following query ```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'; ``` ```output +-------------------------------+---------+-----------+---------------------------------------------------+-----------------------------------------------------+ | 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. ```sql DROP INDEX idx_pl_name ON planetarySystem; ``` ```output Query OK, 0 rows affected (0.36 sec) ``` ### Step 2. Add another index and check the execution plan for the same query + 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; ``` ```output Query OK, 0 rows affected, 1 warning (5.23 sec) ``` ```sql EXPLAIN SELECT pl_name, hostname FROM planetarySystem WHERE pl_name='HAT-P-42 b' and hostname='HAT-P-42'; ``` ```output +------------------------+---------+-----------+----------------------------------------------------------------------+---------------------------------------------------------------------------+ | 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`. ```sql DROP INDEX idx_pl_name_hostname ON planetarySystem; ``` ```output Query OK, 0 rows affected (0.37 sec) ``` ### Step 3. Add an index and check the execution plan for the following query ```sql CREATE INDEX idx_pl_name_hostname_ref ON planetarySystem(pl_name, hostname, ref_id); ANALYZE TABLE planetarySystem; ``` ```output Query OK, 0 rows affected, 1 warning (2.88 sec) ``` ```sql EXPLAIN SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where hostname='11 Com' AND ref_id=2048; ``` ```output +---------------------------+----------+-----------+-----------------------+-----------------------------------------------------------------------------------------------------------------------------------------+ | 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. The index `idx_pl_name_hostname_ref` is composed in the order of `pl_name`, `hostname`, and `ref_id`. If the filtering condition does not include `pl_name`, then the index can not be used. ### Step 4. Check the execution plan for the following query ```sql EXPLAIN SELECT pl_name, hostname, sy_snum, sy_pnum FROM planetarySystem where pl_name='11 Com b' AND ref_id=2048; ``` ```output +---------------------------------+---------+-----------+----------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+ | 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`. ### Step 5. Check the execution plan for the following query ```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; ``` ```output +---------------------------------+---------+-----------+----------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+ | 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. ### Step 6. Check the execution plan for the following query ```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; ``` ```output +---------------------------------+---------+-----------+----------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+ | 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. ### Step 7. Check the execution plan for the following query ```sql EXPLAIN SELECT id, pl_name, hostname FROM planetarySystem WHERE hostname = '11 Com' AND ref_id=2048; ``` ```output +---------------------------+----------+-----------+----------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------+ | 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. ```sql DROP INDEX idx_pl_name_hostname_ref ON planetarySystem; ``` ```output Query OK, 0 rows affected (0.36 sec) ``` ### Step 8. Add an index and check the execution plan for the following query + 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; ``` ```output Query OK, 0 rows affected, 1 warning (3.26 sec) ``` ```sql EXPLAIN SELECT id, pl_name, hostname FROM planetarySystem WHERE id=835868090839969012 OR ref_id=2048; ``` ```output +----------------------------------+---------+-----------+-------------------------------------------------+--------------------------------------------------------------------------------------------------+ | 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. ```sql DROP INDEX idx_ref_id ON planetarySystem; ``` ```output Query OK, 0 rows affected (0.36 sec) ``` ## Module 3: Indexing Strategies with ORDER BY + In this module, we optimize the following query ```sql SELECT pl_name, hostname, disc_year FROM planetarySystem WHERE hostname='14 Her' ORDER BY disc_year DESC; ``` ### Step 1. Check the execution plan for the following query ```sql EXPLAIN SELECT pl_name, hostname, disc_year FROM planetarySystem WHERE hostname='14 Her' ORDER BY disc_year DESC; ``` ```output +---------------------------+----------+-----------+-----------------------+-------------------------------------------------+ | 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. ### Step 2. Add an index and check the execution plan for the following query ```sql ALTER TABLE planetarySystem ADD INDEX idx_hostname(hostname); ANALYZE TABLE planetarySystem; ``` ```output Query OK, 0 rows affected, 1 warning (3.41 sec) ``` ```sql EXPLAIN SELECT pl_name, hostname, disc_year FROM planetarySystem WHERE hostname='14 Her' ORDER BY disc_year DESC; ``` ```output +----------------------------------+---------+-----------+-----------------------------------------------------+---------------------------------------------+ | 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. ### Step 3. Add an index and check the execution plan for the following query + Remove the sort operation by making the changes identified in the previous step. ```sql ALTER TABLE planetarySystem DROP INDEX idx_hostname; ALTER TABLE planetarySystem ADD INDEX idx_hostname_disc_year(hostname, disc_year); ANALYZE TABLE planetarySystem; ``` ```output Query OK, 0 rows affected (3.06 sec) ``` ```sql EXPLAIN SELECT pl_name, hostname, disc_year FROM planetarySystem WHERE hostname='14 Her' ORDER BY disc_year DESC; ``` ```output +----------------------------------+---------+-----------+--------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------+ | 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. + 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. ### Step 4. Add an index and check the execution plan for the following query ```sql ALTER TABLE planetarySystem DROP INDEX idx_hostname_disc_year; ALTER TABLE planetarySystem ADD INDEX idx_hostname_disc_year_pl_name(hostname, disc_year, pl_name); ANALYZE TABLE planetarySystem; ``` ```output Query OK, 0 rows affected (0.35 sec) ``` ```sql EXPLAIN SELECT pl_name, hostname, disc_year FROM planetarySystem WHERE hostname='14 Her' ORDER BY disc_year DESC; ``` ```output +-------------------------+---------+-----------+-------------------------------------------------------------------------------------------+--------------------------------------------------+ | 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. ### Step 5. Drop the index + Drop any indexes that you added in this practice to return the `planetarySystem` table to its original state. ```sql ALTER TABLE planetarySystem DROP INDEX idx_hostname_disc_year_pl_name; ``` ```output Query OK, 0 rows affected (0.35 sec) ``` ## Module 4: Indexing Strategies with JOIN + In this module, we optimize the following two queries: ```sql 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; ``` ```sql SELECT * FROM planetarySystem WHERE ref_id IN ( SELECT ID FROM parameterReference WHERE ref_name LIKE 'Rosenthal%'); ``` ### Step 1. Check the execution plan for the following query ```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; ``` ```output +------------------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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. ### Step 2. Add indexes and check the execution plan for the following query ```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; ``` ```output Query OK, 0 rows affected, 1 warning (0.63 sec) ``` ```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; ``` ```output +------------------------------------------+---------+-----------+------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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. ### Step 3. Add indexes and check the execution plan for the following query ```sql CREATE INDEX idx_ref_id ON planetarySystem(ref_id); CREATE INDEX idx_df_id ON planetarySystem(df_id); ANALYZE TABLE planetarySystem; ``` ```output Query OK, 0 rows affected, 1 warning (4.00 sec) ``` ```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; ``` ```output +------------------------------------------+---------+-----------+------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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: ```sql 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 +----------+ | COUNT(*) | +----------+ | 2 | +----------+ 1 row in set (0.01 sec) +----------+ | COUNT(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec) +----------+ | 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. ### Step 4. Drop the indexes ```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; ``` ```output Query OK, 0 rows affected (0.52 sec) ``` ### Step 5. Check the execution plan for the following query ```sql 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`. ### Step 6. Add indexes and check the execution plan for the following query ```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; ``` ```output Query OK, 0 rows affected, 1 warning (0.83 sec) ``` ```sql EXPLAIN SELECT * FROM planetarySystem WHERE ref_id IN ( SELECT ID FROM parameterReference WHERE ref_name LIKE 'Rosenthal%'); ``` ```output +----------------------------------+---------+-----------+--------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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. ### Step 7. Drop the indexes ```sql DROP INDEX idx_ref_id ON planetarysystem; DROP INDEX idx_ref_name ON parameterreference; ``` ```output Query OK, 0 rows affected (0.52 sec) ``` ## End of Exercises