## Module 2: Using EXPLAIN Command + In this exercise, you will you examine the TiDB optimizer's execution plan for several different SELECT queries by using the EXPLAIN command. ### Step 1. Execute the following statement to view the EXPLAIN output ```sql EXPLAIN SELECT * FROM parameterReference WHERE id=100; ``` ```output tidb>EXPLAIN SELECT * FROM parameterReference WHERE id=100; +-------------+---------+------+--------------------------+---------------+ | id | estRows | task | access object | operator info | +-------------+---------+------+--------------------------+---------------+ | Point_Get_1 | 1.00 | root | table:parameterReference | handle:100 | +-------------+---------+------+--------------------------+---------------+ 1 row in set (0.00 sec) ``` + What is the access method for this query? + Answer: `Point_Get` + How many rows must TiDB read to perform this query? + Answer: 1 + Does this access strategy result in good performance? Why? + Answer: Yes. The `Point_Get` type applies when you compare all parts of a `PRIMARY KEY` or `UNIQUE Index` to constant values. In this example, the table has at the most one matching row. The optimizer treats this row's column values as constants and reads them only once. In TiDB, `Point_Get` does not require compiler to generate an execution plan. As data is stored in TiKV in key-value format, we can directly retrieve data using `Point_Get` when the `WHERE` condition is a primary key or unique index. ### Step 2. Execute the following statement to view the EXPLAIN output ```sql EXPLAIN SELECT * FROM parameterReference WHERE id=100+5; ``` ```output tidb>EXPLAIN SELECT * FROM parameterReference WHERE id=100+5; +-------------+---------+------+--------------------------+---------------+ | id | estRows | task | access object | operator info | +-------------+---------+------+--------------------------+---------------+ | Point_Get_5 | 1.00 | root | table:parameterReference | handle:105 | +-------------+---------+------+--------------------------+---------------+ 1 row in set (0.00 sec) ``` + Is there any difference in the execution plan for this query, when compared to the query that you looked at in step 1? Why? + Answer: No. The optimizer evaluates the expression 100+5 before executing the query. ### Step 3. Execute the following statement to view the EXPLAIN output ```sql EXPLAIN SELECT * FROM parameterReference WHERE id-5=100; ``` ```output tidb>EXPLAIN SELECT * FROM parameterReference WHERE id-5=100; +-------------------------+---------+-----------+--------------------------+---------------------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------+---------+-----------+--------------------------+---------------------------------------------------+ | TableReader_7 | 1644.00 | root | | data:Selection_6 | | └─Selection_6 | 1644.00 | cop[tikv] | | eq(minus(universe.parameterreference.id, 5), 100) | | └─TableFullScan_5 | 2055.00 | cop[tikv] | table:parameterReference | keep order:false | +-------------------------+---------+-----------+--------------------------+---------------------------------------------------+ 3 rows in set (0.00 sec) ``` + Is there any difference in the execution plan for this query, when compared to the query that you looked at in step 1 and 2? Why? + Answer: Yes. The optimizer cannot reduce the expression `ID-5=100` to a single value before executing the query. Thus, it must scan the full table (type: TableFullScan). The number of rows is an estimate based on statistics and may not equal to the exact number of rows in the `parameterReference` table. ### Step 4. Display the optimizer cost for the query in JSON format ```sql CREATE INDEX idx_host_name_ref ON planetarySystem(hostname, pl_name, ref_id); EXPLAIN FORMAT = "tidb_json" SELECT pl_name, sy_snum, sy_pnum FROM planetarySystem WHERE pl_name='11 Com b' AND hostname='11 Com' AND ref_id=71\G DROP INDEX idx_host_name_ref on planetarySystem; ``` ```output *************************** 1. row *************************** TiDB_JSON: [ { "id": "Projection_4", "estRows": "1.00", "taskType": "root", "operatorInfo": "universe.planetarysystem.pl_name, universe.planetarysystem.sy_snum, universe.planetarysystem.sy_pnum", "subOperators": [ { "id": "IndexLookUp_10", "estRows": "1.00", "taskType": "root", "subOperators": [ { "id": "IndexRangeScan_8(Build)", "estRows": "1.00", "taskType": "cop[tikv]", "accessObject": "table:planetarySystem, index:idx_host_name_ref(hostname, pl_name, ref_id)", "operatorInfo": "range:[\"11 Com\" \"11 Com b\" 71,\"11 Com\" \"11 Com b\" 71], keep order:false" }, { "id": "TableRowIDScan_9(Probe)", "estRows": "1.00", "taskType": "cop[tikv]", "accessObject": "table:planetarySystem", "operatorInfo": "keep order:false" } ] } ] } ] 1 row in set (0.01 sec) ``` + What cost did the optimizer calculate for this query? + Answer: 1.0 (the value of the "estRows" field in the JSON output. The cost info JSON object displays a more detailed breakdown of the cost.) + Which columns must TiDB access to resolve the query? + Answer: The `universe.planetarysystem.pl_name`, `universe.planetarysystem.sy_snum`, `universe.planetarysystem.sy_pnum` columns (the elements in the JSON "operatorInfo" array field) + Which operations are carried out on TiKV? + Answer: The `IndexRangeScan` and `TableRowIDScan` are carried out on TiKV (the value of the `taskType` field in the JSON output is `cop[tikv]`). + Which operations are carried out on the TiDB Server? + Answer: The `IndexLookUp` is carried out on TiDB Server (the value of the "taskType" field in the JSON output is "root").