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