# Lab 6: Optimizer Hints and SQL Plan Management (SPM)
### Overview
+ In this exercise, you use SQL hints and SQL Plan Management (SPM) to selection a specific execution plan.
### 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: Optimizer Hints and SQL Plan Management (SPM)
### Overview
+ In this exercise, you use SQL hints and SQL Plan Management (SPM) to selection a specific execution plan.
### Duration
+ This exercise should take you approximately 20 minutes to complete.
### Tasks
+ Select a specific execution plan using hints.
+ Select a specific execution plan without modifying SQL statements using SQL Plan Management (SPM)
### 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. Log into TiDB.
```command
$ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
```
2. Make `test` the current database.
```command
USE test;
```
```output
tidb>USE test;
Database changed
```
3. Execute the following statement to create table t1 and t2 and their indexes for your exercise.
```command
CREATE TABLE t1(a int, b int, c varchar(10), d varchar(20));
CREATE TABLE t2(a int, b int, c varchar(10), d varchar(20));
CREATE INDEX t1_idx_1 ON t1(a);
CREATE INDEX t1_idx_2 ON t1(a,b,c);
CREATE INDEX t2_idx_1 ON t2(a);
CREATE INDEX t2_idx_2 ON t2(a,b,c);
```
```output
tidb>CREATE TABLE t1(a int, b int, c varchar(10), d varchar(20));
Query OK, 0 rows affected (0.54 sec)
tidb>CREATE TABLE t2(a int, b int, c varchar(10), d varchar(20));
Query OK, 0 rows affected (0.52 sec)
tidb>CREATE INDEX t1_idx_1 ON t1(a);
Query OK, 0 rows affected (1.03 sec)
tidb>CREATE INDEX t1_idx_2 ON t1(a,b,c);
Query OK, 0 rows affected (1.03 sec)
tidb>CREATE INDEX t2_idx_1 ON t2(a);
Query OK, 0 rows affected (1.02 sec)
tidb>CREATE INDEX t2_idx_2 ON t2(a,b,c);
Query OK, 0 rows affected (1.03 sec)
```
4. Exit the TiDB Session to Linux terminal window and insert date into t1 and t2. The insertion command may take a few minutes to run.
```command
exit
```
```output
tidb>exit
Bye
```
```command
for i in `seq 10000`; do mysql -h${HOST_DB1_PRIVATE_IP} -P4000 -uroot -e "INSERT INTO test.t1 VALUES($i, FLOOR(RAND()*10000000), SUBSTRING(MD5(RAND()), 1, 5), SUBSTRING(MD5(RAND()), 1, 10))"; done;
for i in `seq 10000`; do mysql -h${HOST_DB1_PRIVATE_IP} -P4000 -uroot -e "INSERT INTO test.t2 VALUES($i, FLOOR(RAND()*10000000), SUBSTRING(MD5(RAND()), 1, 5), SUBSTRING(MD5(RAND()), 1, 10))"; done;
```
```output
$for i in `seq 10000`; do mysql -h${HOST_DB1_PRIVATE_IP} -P4000 -uroot -e "INSERT INTO test.t1 VALUES($i, FLOOR(RAND()*10000000), SUBSTRING(MD5(RAND()), 1, 5), SUBSTRING(MD5(RAND()), 1, 10))"; done;
$for i in `seq 10000`; do mysql -h${HOST_DB1_PRIVATE_IP} -P4000 -uroot -e "INSERT INTO test.t2 VALUES($i, FLOOR(RAND()*10000000), SUBSTRING(MD5(RAND()), 1, 5), SUBSTRING(MD5(RAND()), 1, 10))"; done;
```
5. Start an interactive TiDB session again.
```command
$ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
```
6. Make `test` the current database.
```command
USE test;
```
```output
tidb>USE test;
Database changed
```
7. Collect the statistic of table `t1` and `t2`.
```command
ANALYZE TABLE t1;
ANALYZE TABLE t2;
```
```output
tidb> ANALYZE TABLE t1;
Query OK, 0 rows affected, 1 warning (1.40 sec)
tidb> ANALYZE TABLE t2;
Query OK, 0 rows affected, 1 warning (1.41 sec)
```
8. Execute the following statement to view the EXPLAIN output for a query against the `t1` and `t2` table.
```command
EXPLAIN SELECT t1.a, t1.b, t1.c, t2.b FROM t1, t2 WHERE t1.a = t2.a AND t1.b > t2.b ORDER BY t1.b, t2.b;
```
```output
tidb>EXPLAIN SELECT t1.a, t1.b, t1.c, t2.b FROM t1, t2 WHERE t1.a = t2.a AND t1.b > t2.b ORDER BY t1.b, t2.b;
+--------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------+
| Sort_8 | 10000.00 | root | | test.t1.b, test.t2.b |
| └─MergeJoin_11 | 10000.00 | root | | inner join, left key:test.t1.a, right key:test.t2.a, other cond:gt(test.t1.b, test.t2.b) |
| ├─IndexReader_42(Build) | 10000.00 | root | | index:Selection_41 |
| │ └─Selection_41 | 10000.00 | cop[tikv] | | not(isnull(test.t2.b)) |
| │ └─IndexFullScan_40 | 10000.00 | cop[tikv] | table:t2, index:t2_idx_2(a, b, c) | keep order:true |
| └─IndexReader_39(Probe) | 10000.00 | root | | index:Selection_38 |
| └─Selection_38 | 10000.00 | cop[tikv] | | not(isnull(test.t1.b)) |
| └─IndexFullScan_37 | 10000.00 | cop[tikv] | table:t1, index:t1_idx_2(a, b, c) | keep order:true |
+--------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------+
8 rows in set (0.02 sec)
```
9. Use SQL Hint, so that index `t2_idx_1` and `IndexJoin` can be used, instead of `t2_idx_2` and `MergeJoin`.
```command
EXPLAIN SELECT t1.a, t1.b, t1.c, t2.b FROM t1, t2 USE INDEX(t2_idx_1) WHERE t1.a = t2.a AND t1.b > t2.b ORDER BY t1.b, t2.b;
```
```output
tidb>EXPLAIN SELECT t1.a, t1.b, t1.c, t2.b FROM t1, t2 USE INDEX(t2_idx_1) WHERE t1.a = t2.a AND t1.b > t2.b ORDER BY t1.b, t2.b;
+---------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Sort_8 | 10000.00 | root | | test.t1.b, test.t2.b |
| └─IndexJoin_18 | 10000.00 | root | | inner join, inner:IndexLookUp_17, outer key:test.t1.a, inner key:test.t2.a, equal cond:eq(test.t1.a, test.t2.a), other cond:gt(test.t1.b, test.t2.b) |
| ├─IndexReader_52(Build) | 10000.00 | root | | index:Selection_51 |
| │ └─Selection_51 | 10000.00 | cop[tikv] | | not(isnull(test.t1.b)) |
| │ └─IndexFullScan_50 | 10000.00 | cop[tikv] | table:t1, index:t1_idx_2(a, b, c) | keep order:false |
| └─IndexLookUp_17(Probe) | 10000.00 | root | | |
| ├─Selection_15(Build) | 10000.00 | cop[tikv] | | not(isnull(test.t2.a)) |
| │ └─IndexRangeScan_13 | 10000.00 | cop[tikv] | table:t2, index:t2_idx_1(a) | range: decided by [eq(test.t2.a, test.t1.a)], keep order:false |
| └─Selection_16(Probe) | 10000.00 | cop[tikv] | | not(isnull(test.t2.b)) |
| └─TableRowIDScan_14 | 10000.00 | cop[tikv] | table:t2 | keep order:false |
+---------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)
```
10. Use SQL Plan Management (SPM) to let all SQL to use index `t2_idx_1` and use `HashJoin` all queries similar to the one in step 8 without changing the SQL statements in the running program.
```command
CREATE GLOBAL BINDING FOR
SELECT t1.a, t1.b, t1.c, t2.b FROM t1, t2 WHERE t1.a = t2.a AND t1.b > t2.b ORDER BY t1.b, t2.b
USING
SELECT t1.a, t1.b, t1.c, t2.b FROM t1, t2 USE INDEX(t2_idx_1) WHERE t1.a = t2.a AND t1.b > t2.b ORDER BY t1.b, t2.b;
```
```output
tidb>CREATE GLOBAL BINDING FOR
-> SELECT t1.a, t1.b, t1.c, t2.b FROM t1, t2 WHERE t1.a = t2.a AND t1.b > t2.b ORDER BY t1.b, t2.b
-> USING
-> SELECT t1.a, t1.b, t1.c, t2.b FROM t1, t2 USE INDEX(t2_idx_1) WHERE t1.a = t2.a AND t1.b > t2.b ORDER BY t1.b, t2.b;
Query OK, 0 rows affected (0.01 sec)
```
11. Re-issue the EXPLAIN statement from step 8. Explain the difference in the output.
```command
EXPLAIN SELECT t1.a, t1.b, t1.c, t2.b FROM t1, t2 WHERE t1.a = t2.a AND t1.b > t2.b ORDER BY t1.b, t2.b;
```
```output
tidb>EXPLAIN SELECT t1.a, t1.b, t1.c, t2.b FROM t1, t2 WHERE t1.a = t2.a AND t1.b > t2.b ORDER BY t1.b, t2.b;
+---------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Sort_8 | 10000.00 | root | | test.t1.b, test.t2.b |
| └─IndexJoin_18 | 10000.00 | root | | inner join, inner:IndexLookUp_17, outer key:test.t1.a, inner key:test.t2.a, equal cond:eq(test.t1.a, test.t2.a), other cond:gt(test.t1.b, test.t2.b) |
| ├─IndexReader_52(Build) | 10000.00 | root | | index:Selection_51 |
| │ └─Selection_51 | 10000.00 | cop[tikv] | | not(isnull(test.t1.b)) |
| │ └─IndexFullScan_50 | 10000.00 | cop[tikv] | table:t1, index:t1_idx_2(a, b, c) | keep order:false |
| └─IndexLookUp_17(Probe) | 10000.00 | root | | |
| ├─Selection_15(Build) | 10000.00 | cop[tikv] | | not(isnull(test.t2.a)) |
| │ └─IndexRangeScan_13 | 10000.00 | cop[tikv] | table:t2, index:t2_idx_1(a) | range: decided by [eq(test.t2.a, test.t1.a)], keep order:false |
| └─Selection_16(Probe) | 10000.00 | cop[tikv] | | not(isnull(test.t2.b)) |
| └─TableRowIDScan_14 | 10000.00 | cop[tikv] | table:t2 | keep order:false |
+---------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)
```
12. Open another new Linux terminal window and start an interactive TiDB session.
```command
$ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
```
13. Make `test` the current database.
```command
USE test;
```
```output
tidb>USE test;
Database changed
```
14. In the new Linux terminal, re-issue the EXPLAIN statement from step 8. Explain the difference in the output.
```command
EXPLAIN SELECT t1.a, t1.b, t1.c, t2.b FROM t1, t2 WHERE t1.a = t2.a AND t1.b > t2.b ORDER BY t1.b, t2.b;
```
```output
tidb>EXPLAIN SELECT t1.a, t1.b, t1.c, t2.b FROM t1, t2 WHERE t1.a = t2.a AND t1.b > t2.b ORDER BY t1.b, t2.b;
+---------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Sort_8 | 10000.00 | root | | test.t1.b, test.t2.b |
| └─IndexJoin_18 | 10000.00 | root | | inner join, inner:IndexLookUp_17, outer key:test.t1.a, inner key:test.t2.a, equal cond:eq(test.t1.a, test.t2.a), other cond:gt(test.t1.b, test.t2.b) |
| ├─IndexReader_52(Build) | 10000.00 | root | | index:Selection_51 |
| │ └─Selection_51 | 10000.00 | cop[tikv] | | not(isnull(test.t1.b)) |
| │ └─IndexFullScan_50 | 10000.00 | cop[tikv] | table:t1, index:t1_idx_2(a, b, c) | keep order:false |
| └─IndexLookUp_17(Probe) | 10000.00 | root | | |
| ├─Selection_15(Build) | 10000.00 | cop[tikv] | | not(isnull(test.t2.a)) |
| │ └─IndexRangeScan_13 | 10000.00 | cop[tikv] | table:t2, index:t2_idx_1(a) | range: decided by [eq(test.t2.a, test.t1.a)], keep order:false |
| └─Selection_16(Probe) | 10000.00 | cop[tikv] | | not(isnull(test.t2.b)) |
| └─TableRowIDScan_14 | 10000.00 | cop[tikv] | table:t2 | keep order:false |
+---------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)
```
15. Check the global bindings in any linux terminal.
```command
SHOW GLOBAL BINDINGS WHERE ORIGINAL_SQL LIKE '%t1%t2%'\G
```
```output
tidb>SHOW GLOBAL BINDINGS WHERE ORIGINAL_SQL LIKE '%t1%t2%'\G
*************************** 1. row ***************************
Original_sql: select `t1` . `a` , `t1` . `b` , `t1` . `c` , `t2` . `b` from ( `test` . `t1` ) join `test` . `t2` where `t1` . `a` = `t2` . `a` and `t1` . `b` > `t2` . `b` order by `t1` . `b` , `t2` . `b`
Bind_sql: SELECT `t1`.`a`,`t1`.`b`,`t1`.`c`,`t2`.`b` FROM (`test`.`t1`) JOIN `test`.`t2` USE INDEX (`t2_idx_1`) WHERE `t1`.`a` = `t2`.`a` AND `t1`.`b` > `t2`.`b` ORDER BY `t1`.`b`,`t2`.`b`
Default_db: test
Status: enabled
Create_time: 2023-12-04 12:22:50.927
Update_time: 2023-12-04 12:22:50.927
Charset: utf8
Collation: utf8_general_ci
Source: manual
Sql_digest: fc14be07f408211ab8bcba115f45f2c602250e16e3d540960fceb7238c4e9d87
Plan_digest:
1 row in set (0.00 sec)
```
16. Drop the global bindings in any linux terminal.
```command
DROP GLOBAL BINDING FOR SQL DIGEST 'fc14be07f408211ab8bcba115f45f2c602250e16e3d540960fceb7238c4e9d87';
```
```output
tidb>DROP GLOBAL BINDING FOR SQL DIGEST 'fc14be07f408211ab8bcba115f45f2c602250e16e3d540960fceb7238c4e9d87';
Query OK, 1 row affected (0.01 sec)
```
17. In any Linux terminal, re-issue the EXPLAIN statement from step 8. Explain the difference in the output.
```command
EXPLAIN SELECT t1.a, t1.b, t1.c, t2.b FROM t1, t2 WHERE t1.a = t2.a AND t1.b > t2.b ORDER BY t1.b, t2.b;
```
```output
tidb>EXPLAIN SELECT t1.a, t1.b, t1.c, t2.b FROM t1, t2 WHERE t1.a = t2.a AND t1.b > t2.b ORDER BY t1.b, t2.b;
+--------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------+
| Sort_8 | 10000.00 | root | | test.t1.b, test.t2.b |
| └─MergeJoin_11 | 10000.00 | root | | inner join, left key:test.t1.a, right key:test.t2.a, other cond:gt(test.t1.b, test.t2.b) |
| ├─IndexReader_42(Build) | 10000.00 | root | | index:Selection_41 |
| │ └─Selection_41 | 10000.00 | cop[tikv] | | not(isnull(test.t2.b)) |
| │ └─IndexFullScan_40 | 10000.00 | cop[tikv] | table:t2, index:t2_idx_2(a, b, c) | keep order:true |
| └─IndexReader_39(Probe) | 10000.00 | root | | index:Selection_38 |
| └─Selection_38 | 10000.00 | cop[tikv] | | not(isnull(test.t1.b)) |
| └─IndexFullScan_37 | 10000.00 | cop[tikv] | table:t1, index:t1_idx_2(a, b, c) | keep order:true |
+--------------------------------+----------+-----------+-----------------------------------+------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)
```
18. Clean up the table t1 and t2.
```command
DROP TABLE t1,t2;
```
```output
tidb>DROP TABLE t1,t2;
Query OK, 0 rows affected (1.06 sec)
```
## End of Exercises