SQL Hint Leading Not Effective

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

Original topic: sql hint leading 不生效

| username: Raymond

explain analyze select /*+ LEADING(region,nation,customer,orders,lineitem,supplier) */
         n_name,
         sum(l_extendedprice * (1 - l_discount)) as revenue
 from
         customer,
         orders,
         lineitem,
         supplier,
         nation,
         region
 where
         c_custkey = o_custkey
         and l_orderkey = o_orderkey
         and l_suppkey = s_suppkey
         and c_nationkey = n_nationkey
         and s_nationkey = n_nationkey
         and n_regionkey = r_regionkey
         and r_name = 'ASIA'
         and o_orderdate >= date '1994-01-01'
         and o_orderdate < date '1994-01-01' + interval '1' year
 group by
         n_name
 order by
         revenue desc;

+------------------------------------------------+-------------+----------+-----------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+
| id                                             | estRows     | actRows  | task      | access object  | execution info                                                                                                                                                                                                                                                                                                                                                              | operator info                                                                                                                                                           | memory     | disk    |
+------------------------------------------------+-------------+----------+-----------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+
| Sort_16                                        | 20.00       | 5        | root      |                | time:4m5.8s, loops:2                                                                                                                                                                                                                                                                                                                                                        | Column#48:desc                                                                                                                                                          | 2.69 KB    | 0 Bytes |
| └─Projection_18                                | 20.00       | 5        | root      |                | time:4m5.8s, loops:2, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                       | tpch.nation.n_name, Column#48                                                                                                                                           | 2.65 KB    | N/A     |
|   └─HashAgg_19                                 | 20.00       | 5        | root      |                | time:4m5.8s, loops:2                                                                                                                                                                                                                                                                                                                                                        | group by:Column#51, funcs:sum(Column#49)->Column#48, funcs:firstrow(Column#50)->tpch.nation.n_name                                                                      | 79.4 KB    | 0 Bytes |
|     └─Projection_126                           | 4545.43     | 145977   | root      |                | time:4m5.8s, loops:146, Concurrency:5                                                                                                                                                                                                                                                                                                                                       | mul(tpch.lineitem.l_extendedprice, minus(1, tpch.lineitem.l_discount))->Column#49, tpch.nation.n_name, tpch.nation.n_name                                               | 888.9 KB   | N/A     |
|       └─HashJoin_31                            | 4545.43     | 145977   | root      |                | time:4m5.8s, loops:146, build_hash_table:{total:2.27ms, fetch:2.27ms, build:3.16µs}, probe:{concurrency:5, total:20m29.1s, max:4m5.8s, probe:344.3ms, fetch:20m28.8s}                                                                                                                                                                                                       | inner join, equal:[eq(tpch.nation.n_regionkey, tpch.region.r_regionkey)]                                                                                                | 25.7 KB    | 0 Bytes |
|         ├─TableReader_124(Build)               | 0.01        | 1        | root      |                | time:2.25ms, loops:2, cop_task: {num: 1, max: 2.58ms, proc_keys: 5, rpc_num: 1, rpc_time: 2.57ms, copr_cache: disabled, distsql_concurrency: 15}                                                                                                                                                                                                                            | data:Selection_123                                                                                                                                                      | 259 Bytes  | N/A     |
|         │ └─Selection_123                      | 0.01        | 1        | cop[tikv] |                | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 5, total_process_keys_size: 559, total_keys: 6, get_snapshot_time: 8.9µs, rocksdb: {key_skipped_count: 5, block: {cache_hit_count: 6}}}                                                                                                                                                                     | eq(tpch.region.r_name, "ASIA")                                                                                                                                          | N/A        | N/A     |
|         │   └─TableFullScan_122                | 5.00        | 5        | cop[tikv] | table:region   | tikv_task:{time:0s, loops:1}                                                                                                                                                                                                                                                                                                                                                | keep order:false, stats:pseudo                                                                                                                                          | N/A        | N/A     |
|         └─HashJoin_41(Probe)                   | 18181718.62 | 726970   | root      |                | time:4m5.8s, loops:714, build_hash_table:{total:1.57ms, fetch:1.56ms, build:9.74µs}, probe:{concurrency:5, total:20m29.1s, max:4m5.8s, probe:3.02s, fetch:20m26.1s}                                                                                                                                                                                                         | inner join, equal:[eq(tpch.customer.c_nationkey, tpch.nation.n_nationkey) eq(tpch.supplier.s_nationkey, tpch.nation.n_nationkey)]                                       | 33.9 KB    | 0 Bytes |
|           ├─TableReader_121(Build)             | 25.00       | 25       | root      |                | time:1.55ms, loops:2, cop_task: {num: 1, max: 2.06ms, proc_keys: 25, rpc_num: 1, rpc_time: 1.95ms, copr_cache: disabled, distsql_concurrency: 15}                                                                                                                                                                                                                           | data:TableFullScan_120                                                                                                                                                  | 1020 Bytes | N/A     |
|           │ └─TableFullScan_120                | 25.00       | 25       | cop[tikv] | table:nation   | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 25, total_process_keys_size: 3109, total_keys: 26, get_snapshot_time: 11µs, rocksdb: {key_skipped_count: 25, block: {cache_hit_count: 6}}}                                                                                                                                                                  | keep order:false, stats:pseudo                                                                                                                                          | N/A        | N/A     |
|           └─HashJoin_52(Probe)                 | 18181718.62 | 18173018 | root      |                | time:4m5.8s, loops:17749, build_hash_table:{total:313.1ms, fetch:272.3ms, build:40.9ms}, probe:{concurrency:5, total:20m29.1s, max:4m5.8s, probe:42s, fetch:19m47.1s}                                                                                                                                                                                                       | inner join, equal:[eq(tpch.lineitem.l_suppkey, tpch.supplier.s_suppkey)]                                                                                                | 15.1 MB    | 0 Bytes |
|             ├─TableReader_119(Build)           | 200000.00   | 200000   | root      |                | time:272.3ms, loops:197, cop_task: {num: 12, max: 73.5ms, min: 1.43ms, avg: 25.7ms, p95: 73.5ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 223ms, tot_wait: 3ms, rpc_num: 12, rpc_time: 308.6ms, copr_cache: disabled, distsql_concurrency: 15}                                                                                                                 | data:TableFullScan_118                                                                                                                                                  | 1.53 MB    | N/A     |
|             │ └─TableFullScan_118              | 200000.00   | 200000   | cop[tikv] | table:supplier | tikv_task:{proc max:58ms, min:0s, avg: 19.1ms, p80:38ms, p95:58ms, iters:242, tasks:12}, scan_detail: {total_process_keys: 200000, total_process_keys_size: 36284916, total_keys: 200012, get_snapshot_time: 3.86ms, rocksdb: {key_skipped_count: 200000, block: {cache_hit_count: 668}}}                                                                                   | keep order:false                                                                                                                                                        | N/A        | N/A     |
|             └─IndexHashJoin_61(Probe)          | 17905356.64 | 18173018 | root      |                | time:4m5.8s, loops:17750, inner:{total:19m57.6s, concurrency:5, task:186, construct:4.93s, fetch:19m29.7s, build:602.2ms, join:23s}                                                                                                                                                                                                                                         | inner join, inner:TableReader_56, outer key:tpch.orders.o_orderkey, inner key:tpch.lineitem.l_orderkey, equal cond:eq(tpch.orders.o_orderkey, tpch.lineitem.l_orderkey) | 61.4 MB    | N/A     |
|               ├─HashJoin_104(Build)            | 4470018.16  | 4543127  | root      |                | time:14.7s, loops:4441, build_hash_table:{total:5.31s, fetch:4.26s, build:1.06s}, probe:{concurrency:5, total:19m36.9s, max:3m55.4s, probe:18m36.5s, fetch:1m0.4s}                                                                                                                                                                                                          | inner join, equal:[eq(tpch.customer.c_custkey, tpch.orders.o_custkey)]                                                                                                  | 235.6 MB   | 0 Bytes |
|               │ ├─TableReader_112(Build)       | 3000000.00  | 3000000  | root      |                | time:4.24s, loops:2937, cop_task: {num: 116, max: 830ms, min: 10.4ms, avg: 306.4ms, p95: 711ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 22.6s, tot_wait: 9.91s, rpc_num: 116, rpc_time: 35.5s, copr_cache: disabled, distsql_concurrency: 15}                                                                                                                 | data:TableFullScan_111                                                                                                                                                  | 1.53 MB    | N/A     |
|               │ │ └─TableFullScan_111          | 3000000.00  | 3000000  | cop[tikv] | table:customer | tikv_task:{proc max:585ms, min:2ms, avg: 195.1ms, p80:373ms, p95:475ms, iters:3387, tasks:116}, scan_detail: {total_process_keys: 3000000, total_process_keys_size: 610451426, total_keys: 3000116, get_snapshot_time: 2.01ms, rocksdb: {key_skipped_count: 3000000, block: {cache_hit_count: 682, read_count: 9969, read_byte: 222.8 MB, read_time: 12.9s}}}               | keep order:false                                                                                                                                                        | N/A        | N/A     |
|               │ └─TableReader_115(Probe)       | 4470018.16  | 4543127  | root      |                | time:6.23s, loops:4423, cop_task: {num: 511, max: 2.82s, min: 5.23ms, avg: 630.4ms, p95: 1.59s, max_proc_keys: 330720, p95_proc_keys: 217056, tot_proc: 4m21.1s, tot_wait: 38.1s, rpc_num: 511, rpc_time: 5m22.1s, copr_cache: disabled, distsql_concurrency: 15}                                                                                                           | data:Selection_114                                                                                                                                                      | 10.0 MB    | N/A     |
|               │   └─Selection_114              | 4470018.16  | 4543127  | cop[tikv] |                | tikv_task:{proc max:2.62s, min:1ms, avg: 509.7ms, p80:939ms, p95:1.39s, iters:31328, tasks:511}, scan_detail: {total_process_keys: 29955968, total_process_keys_size: 4549646548, total_keys: 29956479, get_snapshot_time: 9.22ms, rocksdb: {key_skipped_count: 29955968, block: {cache_hit_count: 2791, read_count: 75852, read_byte: 1.27 GB, read_time: 1m6.9s}}}        | ge(tpch.orders.o_orderdate, 1994-01-01), lt(tpch.orders.o_orderdate, 1995-01-01)                                                                                        | N/A        | N/A     |
|               │     └─TableFullScan_113        | 29955968.00 | 29955968 | cop[tikv] | table:orders   | tikv_task:{proc max:2.59s, min:1ms, avg: 506.5ms, p80:934ms, p95:1.38s, iters:31328, tasks:511}                                                                                                                                                                                                                                                                             | keep order:false                                                                                                                                                        | N/A        | N/A     |
|               └─TableReader_56(Probe)          | 4470018.16  | 18173018 | root      |                | time:19m20s, loops:18599, cop_task: {num: 6823, max: 2.54s, min: 1.75ms, avg: 561.3ms, p95: 1.21s, max_proc_keys: 32095, p95_proc_keys: 9184, tot_proc: 46m17.5s, tot_wait: 14m4.4s, rpc_num: 6823, rpc_time: 1h3m49.5s, copr_cache: disabled, distsql_concurrency: 15}                                                                                                     | data:TableRangeScan_55                                                                                                                                                  | N/A        | N/A     |
|                 └─TableRangeScan_55            | 4470018.16  | 18173018 | cop[tikv] | table:lineitem | tikv_task:{proc max:2.24s, min:0s, avg: 407.2ms, p80:651ms, p95:1.01s, iters:43509, tasks:6823}, scan_detail: {total_process_keys: 18173018, total_process_keys_size: 3569988017, total_keys: 22721856, get_snapshot_time: 148.7ms, rocksdb: {key_skipped_count: 18173018, block: {cache_hit_count: 26980596, read_count: 368157, read_byte: 5.65 GB, read_time: 12m8.3s}}} | range: decided by [eq(tpch.lineitem.l_orderkey, tpch.orders.o_orderkey)], keep order:false                                                                              | N/A        | N/A     |
+------------------------------------------------+-------------+----------+-----------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+
23 rows in set (4 min 5.83 sec)
	
	
	
	
	
	
 explain select /*+ LEADING(region,nation,customer,orders,lineitem,supplier) */
         n_name,
         sum(l_extendedprice * (1 - l_discount)) as revenue
 from
         customer,
         orders,
         lineitem,
         supplier,
         nation,
         region
 where
         c_custkey = o_custkey
         and l_orderkey = o_orderkey
         and l_suppkey = s_suppkey
         and c_nationkey = n_nationkey
         and s_nationkey = n_nationkey
         and n_regionkey = r_regionkey
         and r_name = 'ASIA'
         and o_orderdate >= date '1994-01-01'
         and o_orderdate < date '1994-01-01' + interval '1' year
 group by
         n_name
 order by
         revenue desc;
+------------------------------------------------+-------------+-----------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                             | estRows     | task      | access object  | operator info                                                                                                                                                           |
+------------------------------------------------+-------------+-----------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Sort_16                                        | 20.00       | root      |                | Column#48:desc                                                                                                                                                          |
| └─Projection_18                                | 20.00       | root      |                | tpch.nation.n_name, Column#48                                                                                                                                           |
|   └─HashAgg_19                                 | 20.00       | root      |                | group by:Column#51, funcs:sum(Column#49)->Column#48, funcs:firstrow(Column#50)->tpch.nation.n_name                                                                      |
|     └─Projection_126                           | 4545.43     | root      |                | mul(tpch.lineitem.l_extendedprice, minus(1, tpch.lineitem.l_discount))->Column#49, tpch.nation.n_name, tpch.nation.n_name                                               |
|       └─HashJoin_31                            | 4545.43     | root      |                | inner join, equal:[eq(tpch.nation.n_regionkey, tpch.region.r_regionkey)]                                                                                                |
|         ├─TableReader_124(Build)               | 0.01        | root      |                | data:Selection_123                                                                                                                                                      |
|         │ └─Selection_123                      | 0.01        | cop[tikv] |                | eq(tpch.region.r_name, "ASIA")                                                                                                                                          |
|         │   └─TableFullScan_122                | 5.00        | cop[tikv] |
| username: 濱崎悟空 | Original post link

Refer to the official documentation:

The LEADING hint will be invalid in the following situations:

  • Multiple LEADING hints are specified
  • The table name specified in the LEADING hint does not exist
  • Duplicate table names are specified in the LEADING hint
  • The optimizer cannot join tables in the order specified by the LEADING hint
  • A straight_join() hint already exists
  • The query contains an outer join and simultaneously specifies a Cartesian product
| username: 小龙虾爱大龙虾 | Original post link

The hint didn’t take effect, and there was no warning. Did you add the -c option when you entered MySQL?

| username: TiDBer_H5NdJb5Q | Original post link

The order table has a where condition, so it needs to go through selection first.

| username: zhh_912 | Original post link

Could it be a syntax incompatibility?

| username: FutureDB | Original post link

Did you use the --comments option when connecting with the MySQL client? If not, it will be treated as a regular comment by default, and hints will not take effect.