# Optimizer Hints - Example ``` tidb> EXPLAIN SELECT * FROM employees, salaries WHERE employees.emp_no = salaries.emp_no; +-------------------------+------------+-----------+-----------------+--------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------+------------+-----------+-----------------+--------------------------------------------------------------------------+ | MergeJoin_8 | 2825288.58 | root | | inner join, left key:db1.employees.emp_no, right key:db1.salaries.emp_no | | ├─TableReader_32(Build) | 2844047.00 | root | | data:TableFullScan_31 | | │ └─TableFullScan_31 | 2844047.00 | cop[tikv] | table:salaries | keep order:true | | └─TableReader_30(Probe) | 300024.00 | root | | data:TableFullScan_29 | | └─TableFullScan_29 | 300024.00 | cop[tikv] | table:employees | keep order:true | +-------------------------+------------+-----------+-----------------+--------------------------------------------------------------------------+ 5 rows in set (0.00 sec) tidb> EXPLAIN SELECT ~!@/*+ HASH_JOIN(employees, salaries) */@!~ * FROM employees, salaries WHERE employees.emp_no = salaries.emp_no; +-------------------------+------------+-----------+-----------------+-------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------+------------+-----------+-----------------+-------------------------------------------------------------------+ | HashJoin_27 | 2825288.58 | root | | inner join, equal:[eq(db1.employees.emp_no, db1.salaries.emp_no)] | | ├─TableReader_29(Build) | 300024.00 | root | | data:TableFullScan_28 | | │ └─TableFullScan_28 | 300024.00 | cop[tikv] | table:employees | keep order:false | | └─TableReader_31(Probe) | 2844047.00 | root | | data:TableFullScan_30 | | └─TableFullScan_30 | 2844047.00 | cop[tikv] | table:salaries | keep order:false | +-------------------------+------------+-----------+-----------------+-------------------------------------------------------------------+ 5 rows in set (0.00 sec) ``` ---------------------------------------------------- If we determine that the execution plan generated by the optimizer for a particular SQL statement is not optimal, and we are currently unable to add or remove indexes from tables or modify the SQL's business logic, the best approach is to adjust the execution plan using `Optimizer Hints`. The syntax for `Optimizer Hints` involves placing the specified execution plan (such as which index to use or how to join tables) within `/*+ ... */` comments. These comments should be placed immediately after the `SELECT`, `UPDATE`, or `DELETE` keywords. It is important to note that `Optimizer Hints` are not supported following the `INSERT` keyword. For example: ``` tidb> EXPLAIN SELECT * FROM employees, salaries WHERE employees.emp_no = salaries.emp_no; +-------------------------+------------+-----------+-----------------+--------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------+------------+-----------+-----------------+--------------------------------------------------------------------------+ | MergeJoin_8 | 2825288.58 | root | | inner join, left key:db1.employees.emp_no, right key:db1.salaries.emp_no | | ├─TableReader_32(Build) | 2844047.00 | root | | data:TableFullScan_31 | | │ └─TableFullScan_31 | 2844047.00 | cop[tikv] | table:salaries | keep order:true | | └─TableReader_30(Probe) | 300024.00 | root | | data:TableFullScan_29 | | └─TableFullScan_29 | 300024.00 | cop[tikv] | table:employees | keep order:true | +-------------------------+------------+-----------+-----------------+--------------------------------------------------------------------------+ 5 rows in set (0.00 sec) tidb> EXPLAIN SELECT /*+ HASH_JOIN(employees, salaries) */ * FROM employees, salaries WHERE employees.emp_no = salaries.emp_no; +-------------------------+------------+-----------+-----------------+-------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------+------------+-----------+-----------------+-------------------------------------------------------------------+ | HashJoin_27 | 2825288.58 | root | | inner join, equal:[eq(db1.employees.emp_no, db1.salaries.emp_no)] | | ├─TableReader_29(Build) | 300024.00 | root | | data:TableFullScan_28 | | │ └─TableFullScan_28 | 300024.00 | cop[tikv] | table:employees | keep order:false | | └─TableReader_31(Probe) | 2844047.00 | root | | data:TableFullScan_30 | | └─TableFullScan_30 | 2844047.00 | cop[tikv] | table:salaries | keep order:false | +-------------------------+------------+-----------+-----------------+-------------------------------------------------------------------+ 5 rows in set (0.00 sec) tidb> EXPLAIN SELECT /*+ INL_JOIN(employees, salaries) */ * FROM employees, salaries WHERE employees.emp_no = salaries.emp_no; +-------------------------+------------+-----------+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------+------------+-----------+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+ | IndexJoin_12 | 2825288.58 | root | | inner join, inner:TableReader_9, outer key:db1.employees.emp_no, inner key:db1.salaries.emp_no, equal cond:eq(db1.employees.emp_no, db1.salaries.emp_no) | | ├─TableReader_27(Build) | 300024.00 | root | | data:TableFullScan_26 | | │ └─TableFullScan_26 | 300024.00 | cop[tikv] | table:employees | keep order:false | | └─TableReader_9(Probe) | 300024.00 | root | | data:TableRangeScan_8 | | └─TableRangeScan_8 | 300024.00 | cop[tikv] | table:salaries | range: decided by [eq(db1.salaries.emp_no, db1.employees.emp_no)], keep order:false | +-------------------------+------------+-----------+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+ 5 rows in set (0.00 sec) ```