Optimizer Hints

TiDB supports optimizer hints, based on the comment-like syntax introduced in MySQL 5.7. i.e. /*+ TIDB_XX(t1, t2) */. Use of optimizer hints is recommended in cases where the TiDB optimizer selects a less optimal query plan.

Note:

MySQL command-line clients earlier than 5.7.7 strip optimizer hints by default. If you want to use the Hint syntax in these earlier versions, add the --comments option when starting the client. For example: mysql -h 127.0.0.1 -P 4000 -uroot --comments.

TIDB_SMJ(t1, t2)

SELECT /*+ TIDB_SMJ(t1, t2) */ * from t1, t2 where t1.id = t2.id

This variable is used to remind the optimizer to use the Sort Merge Join algorithm. This algorithm takes up less memory, but takes longer to execute. It is recommended if the data size is too large, or there’s insufficient system memory.

TIDB_INLJ(t1, t2)

SELECT /*+ TIDB_INLJ(t1, t2) */ * from t1, t2 where t1.id = t2.id

This variable is used to remind the optimizer to use the Index Nested Loop Join algorithm. In some scenarios, this algorithm runs faster and takes up fewer system resources, but may be slower and takes up more system resources in some other scenarios. You can try to use this algorithm in scenarios where the result-set is less than 10,000 rows after the outer table is filtered by the WHERE condition. The parameter in TIDB_INLJ() is the candidate table for the inner table when you create the query plan. For example, TIDB_INLJ (t1) means that TiDB only considers using t1 as the inner table to create a query plan.

TIDB_HJ(t1, t2)

SELECT /*+ TIDB_HJ(t1, t2) */ * from t1, t2 where t1.id = t2.id

This variable is used to remind the optimizer to use the Hash Join algorithm. This algorithm executes threads concurrently. It runs faster but takes up more memory.

MAX_EXECUTION_TIME(N)

The MAX_EXECUTION_TIME hint only applies to SELECT statements. It places a limit N (a timeout value in milliseconds) on how long a statement is permitted to execute before the server terminates it:

MAX_EXECUTION_TIME(N)

Example with a timeout of 1 second (1000 milliseconds):

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t1 INNER JOIN t2 WHERE ...

In addition to this hint, the max_execution_time system variable also limits the execution time of a statement.