TiDB SQL Hint Always Reports Errors

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: tidb sql hint 老是报错

| username: Raymond

I would like to ask the teachers, how to prevent the following statement from using merge join. I added a SQL hint, but it reported an error. I don’t know where the problem is. Could the teachers please advise? Additionally, is there a parameter to disable merge join?

mysql>  explain analyze select  /*+ NO_MERGE_JOIN(customer,orders) */  count(*) from customer where exists (select orders.O_CUSTKEY from orders where customer.C_CUSTKEY=orders.O_CUSTKEY);
+-------------------------------+------------+---------+-----------+-----------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+---------+---------+
| id                            | estRows    | actRows | task      | access object                                 | execution info                                                                                                                                                                                                                                                                                 | operator info                                                                  | memory  | disk    |
+-------------------------------+------------+---------+-----------+-----------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+---------+---------+
| StreamAgg_11                  | 1.00       | 1       | root      |                                               | time:547.7ms, loops:2                                                                                                                                                                                                                                                                          | funcs:count(1)->Column#18                                                      | 8 Bytes | N/A     |
| └─MergeJoin_33                | 120000.00  | 99996   | root      |                                               | time:546ms, loops:99                                                                                                                                                                                                                                                                           | semi join, left key:tpch2.customer.c_custkey, right key:tpch2.orders.o_custkey | 38.5 KB | 0 Bytes |
|   ├─IndexReader_26(Build)     | 1498900.00 | 1498900 | root      |                                               | time:506.1ms, loops:1507, cop_task: {num: 46, max: 51.1ms, min: 1.3ms, avg: 16.8ms, p95: 42ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 536ms, tot_wait: 10ms, rpc_num: 46, rpc_time: 772ms, copr_cache: disabled, distsql_concurrency: 15}                                       | index:IndexFullScan_25                                                         | 5.16 MB | N/A     |
|   │ └─IndexFullScan_25        | 1498900.00 | 1498900 | cop[tikv] | table:orders, index:indexO_CUSTKEY(O_CUSTKEY) | tikv_task:{proc max:39ms, min:0s, avg: 11.7ms, p80:17ms, p95:19ms, iters:1647, tasks:46}, scan_detail: {total_process_keys: 1498900, total_process_keys_size: 68949400, total_keys: 1498946, get_snapshot_time: 3.73ms, rocksdb: {key_skipped_count: 1498900, block: {cache_hit_count: 1080}}} | keep order:true                                                                | N/A     | N/A     |
|   └─TableReader_24(Probe)     | 150000.00  | 150000  | root      |                                               | time:289.7µs, loops:149, cop_task: {num: 11, max: 37.1ms, min: 1.3ms, avg: 11.5ms, p95: 37.1ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 69ms, tot_wait: 8ms, rpc_num: 11, rpc_time: 126.2ms, copr_cache: disabled, distsql_concurrency: 15}                                      | data:TableFullScan_23                                                          | 1.10 MB | N/A     |
|     └─TableFullScan_23        | 150000.00  | 150000  | cop[tikv] | table:customer                                | tikv_task:{proc max:29ms, min:0s, avg: 6.45ms, p80:10ms, p95:29ms, iters:190, tasks:11}, scan_detail: {total_process_keys: 150000, total_process_keys_size: 4050000, total_keys: 150011, get_snapshot_time: 1.44ms, rocksdb: {key_skipped_count: 150000, block: {cache_hit_count: 531}}}       | keep order:true                                                                | N/A     | N/A     |
+-------------------------------+------------+---------+-----------+-----------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+---------+---------+
6 rows in set, 1 warning (0.55 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                   |
+---------+------+-------------------------------------------------------------------------------------------+
| Warning | 1064 | Optimizer hint syntax error at line 1 column 41 near "NO_MERGE_JOIN(customer,orders) */"  |
+---------+------+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

| username: 有猫万事足 | Original post link

Would it be better to use this hint first?
It seems that choosing merge join is the result of exists being rewritten.

| username: changpeng75 | Original post link

exists is not a join operation, so the NO_MERGE_JOIN hint should not be applicable. You can try rewriting it using join.

| username: Raymond | Original post link

I just wanted to test the execution effect of the statement in the case of a semi join, but adding the SQL hint didn’t work.

| username: Raymond | Original post link

All my execution plans show merge join.

| username: Raymond | Original post link

I found that using index join is also not working. Logically, using index join should be OK.

| username: changpeng75 | Original post link

Without this hint, the execution plan should also be MergeJoin.

| username: tidb菜鸟一只 | Original post link

Your orders table has an index on O_CUSTKEY, but the customer table doesn’t have an index on C_CUSTKEY. It should only be able to use the index on the orders table as the driving table to connect to the customer table using a MergeJoin semi-join. It can’t use any other method. Try changing the conditions.

| username: Raymond | Original post link

C_CUSTKEY is the primary key index of the customer.