## 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) ```