LEADING hint cannot take effect when the subquery is the result of joins.

[winoros]

Enhancement

For the SQL select ... from a, b on ... where a.xxx in (select yyy from c, d where ...), we cannot use the LEADING hint to control the join order between the main query and the in subquery.

[winoros]

For the two table case, if we define the query block name for the subquery: a.xxx in (select /*+ QB_NAME(qb_x) */ select yyy from c, d where ...). Then we can use c@qb_x in the LEADING hint.

[harry1129]

There are other cases

MySQL [test]> explain analyze
    -> select /*+ leading(t2@sel_2,t4,t3,t1) */
    -> t1.*,tt2.*,t3.*,t4.*
    -> from 
    -> t1 
    -> join (select * from t2 where col1='AAAA') tt2 on t1.id=tt2.id
    -> join t3 on t1.id=t3.id
    -> join t4 on t1.id=t4.id;
+------------------------------------------+--------------+---------+-----------+-------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| id                                       | estRows      | actRows | task      | access object                 | execution info                                                                                                                                                                                                                                                                                              | operator info                                                                                                                                                                                                          | memory    | disk    |
+------------------------------------------+--------------+---------+-----------+-------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| Projection_19                            | 100000000.00 | 0       | root      |                               | time:14.4ms, loops:1, Concurrency:5                                                                                                                                                                                                                                                                         | test.t1.id, test.t1.col1, test.t1.col2, test.t1.col3, test.t2.id, test.t2.col1, test.t2.col2, test.t2.col3, test.t3.id, test.t3.col1, test.t3.col2, test.t3.col3, test.t4.id, test.t4.col1, test.t4.col2, test.t4.col3 | 299.4 KB  | N/A     |
| └─HashJoin_28                            | 100000000.00 | 0       | root      |                               | time:14.3ms, loops:1, build_hash_table:{total:14.1ms, fetch:12.2ms, build:1.89ms}                                                                                                                                                                                                                           | inner join, equal:[eq(test.t2.id, test.t1.id) eq(test.t3.id, test.t1.id) eq(test.t4.id, test.t1.id)]                                                                                                                   | 798.3 KB  | 0 Bytes |
|   ├─TableReader_45(Build)                | 10000.00     | 7744    | root      |                               | time:12.5ms, loops:8, cop_task: {num: 6, max: 7.01ms, min: 882.6µs, avg: 2.31ms, p95: 7.01ms, max_proc_keys: 3248, p95_proc_keys: 3248, tot_proc: 5ms, rpc_num: 6, rpc_time: 13.8ms, copr_cache_hit_ratio: 0.33, distsql_concurrency: 15}                                                                   | data:TableFullScan_44                                                                                                                                                                                                  | 381.9 KB  | N/A     |
|   │ └─TableFullScan_44                   | 10000.00     | 10000   | cop[tikv] | table:t1                      | tikv_task:{proc max:5ms, min:0s, avg: 1.33ms, p80:1ms, p95:5ms, iters:33, tasks:6}, scan_detail: {total_process_keys: 4944, total_process_keys_size: 355968, total_keys: 4948, get_snapshot_time: 537.4µs, rocksdb: {key_skipped_count: 4944, block: {cache_hit_count: 20}}}                                | keep order:false                                                                                                                                                                                                       | N/A       | N/A     |
|   └─HashJoin_30(Probe)                   | 100000000.00 | 0       | root      |                               | time:8.16ms, loops:1, build_hash_table:{total:7.99ms, fetch:7.75ms, build:245.5µs}                                                                                                                                                                                                                          | CARTESIAN inner join                                                                                                                                                                                                   | 312.8 KB  | 0 Bytes |
|     ├─TableReader_43(Build)              | 10000.00     | 4704    | root      |                               | time:7.8ms, loops:5, cop_task: {num: 5, max: 3.64ms, min: 741.6µs, avg: 1.72ms, p95: 3.64ms, max_proc_keys: 3040, p95_proc_keys: 3040, tot_proc: 1ms, tot_wait: 1ms, rpc_num: 5, rpc_time: 8.54ms, copr_cache_hit_ratio: 0.20, distsql_concurrency: 15}                                                     | data:TableFullScan_42                                                                                                                                                                                                  | 381.9 KB  | N/A     |
|     │ └─TableFullScan_42                 | 10000.00     | 6752    | cop[tikv] | table:t3                      | tikv_task:{proc max:2ms, min:0s, avg: 600µs, p80:2ms, p95:2ms, iters:25, tasks:5}, scan_detail: {total_process_keys: 4736, total_process_keys_size: 340992, total_keys: 4740, get_snapshot_time: 1.03ms, rocksdb: {key_skipped_count: 4736, block: {cache_hit_count: 12}}}                                  | keep order:false                                                                                                                                                                                                       | N/A       | N/A     |
|     └─HashJoin_33(Probe)                 | 10000.00     | 0       | root      |                               | time:6.38ms, loops:1, build_hash_table:{total:1.23ms, fetch:1.23ms, build:0s}                                                                                                                                                                                                                               | CARTESIAN inner join                                                                                                                                                                                                   | 0 Bytes   | 0 Bytes |
|       ├─IndexLookUp_39(Build)            | 1.00         | 0       | root      |                               | time:1.22ms, loops:1                                                                                                                                                                                                                                                                                        |                                                                                                                                                                                                                        | 214 Bytes | N/A     |
|       │ ├─IndexRangeScan_37(Build)       | 1.00         | 0       | cop[tikv] | table:t2, index:t2_col1(col1) | time:1.11ms, loops:1, cop_task: {num: 1, max: 1.07ms, proc_keys: 0, rpc_num: 1, rpc_time: 1.06ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, tikv_task:{time:0s, loops:1}, scan_detail: {total_keys: 1, get_snapshot_time: 564.3µs, rocksdb: {block: {cache_hit_count: 5}}}                      | range:["AAAA","AAAA"], keep order:false                                                                                                                                                                                | N/A       | N/A     |
|       │ └─TableRowIDScan_38(Probe)       | 1.00         | 0       | cop[tikv] | table:t2                      |                                                                                                                                                                                                                                                                                                             | keep order:false                                                                                                                                                                                                       | N/A       | N/A     |
|       └─TableReader_41(Probe)            | 10000.00     | 1024    | root      |                               | time:6.22ms, loops:1, cop_task: {num: 3, max: 3.1ms, min: 1.62ms, avg: 2.23ms, p95: 3.1ms, max_proc_keys: 992, p95_proc_keys: 992, rpc_num: 3, rpc_time: 6.64ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}                                                                                       | data:TableFullScan_40                                                                                                                                                                                                  | 183.1 KB  | N/A     |
|         └─TableFullScan_40               | 10000.00     | 1696    | cop[tikv] | table:t4                      | tikv_task:{proc max:1ms, min:0s, avg: 666.7µs, p80:1ms, p95:1ms, iters:12, tasks:3}, scan_detail: {total_process_keys: 1696, total_process_keys_size: 122112, total_keys: 1699, get_snapshot_time: 606.2µs, rocksdb: {delete_skipped_count: 7767, key_skipped_count: 9463, block: {cache_hit_count: 78}}}   | keep order:false                                                                                                                                                                                                       | N/A       | N/A     |
+------------------------------------------+--------------+---------+-----------+-------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
13 rows in set, 1 warning (0.02 sec)
MySQL [test]> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                             |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1815 | There are no matching table names for (t2) in optimizer hint /*+ LEADING(t2, t4, t3, t1) */. Maybe you can use the table alias name |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL [test]> explain analyze
    -> select /*+ leading(t2@qb2,t4,t3,t1) */
    -> t1.*,tt2.*,t3.*,t4.*
    -> from 
    -> t1 
    -> join (select /*+ qb_name(qb2)*/ * from t2 where col1='AAAA') tt2 on t1.id=tt2.id
    -> join t3 on t1.id=t3.id
    -> join t4 on t1.id=t4.id;
+------------------------------------------+--------------+---------+-----------+-------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| id                                       | estRows      | actRows | task      | access object                 | execution info                                                                                                                                                                                                                                                                                              | operator info                                                                                                                                                                                                          | memory    | disk    |
+------------------------------------------+--------------+---------+-----------+-------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| Projection_19                            | 100000000.00 | 0       | root      |                               | time:10ms, loops:1, Concurrency:5                                                                                                                                                                                                                                                                           | test.t1.id, test.t1.col1, test.t1.col2, test.t1.col3, test.t2.id, test.t2.col1, test.t2.col2, test.t2.col3, test.t3.id, test.t3.col1, test.t3.col2, test.t3.col3, test.t4.id, test.t4.col1, test.t4.col2, test.t4.col3 | 323 KB    | N/A     |
| └─HashJoin_28                            | 100000000.00 | 0       | root      |                               | time:9.8ms, loops:1, build_hash_table:{total:9.65ms, fetch:8.18ms, build:1.47ms}                                                                                                                                                                                                                            | inner join, equal:[eq(test.t2.id, test.t1.id) eq(test.t3.id, test.t1.id) eq(test.t4.id, test.t1.id)]                                                                                                                   | 798.3 KB  | 0 Bytes |
|   ├─TableReader_45(Build)                | 10000.00     | 7744    | root      |                               | time:8.34ms, loops:8, cop_task: {num: 6, max: 2.4ms, min: 891.1µs, avg: 1.58ms, p95: 2.4ms, max_proc_keys: 992, p95_proc_keys: 992, rpc_num: 6, rpc_time: 9.42ms, copr_cache_hit_ratio: 0.50, distsql_concurrency: 15}                                                                                      | data:TableFullScan_44                                                                                                                                                                                                  | 381.8 KB  | N/A     |
|   │ └─TableFullScan_44                   | 10000.00     | 10000   | cop[tikv] | table:t1                      | tikv_task:{proc max:5ms, min:0s, avg: 1.33ms, p80:1ms, p95:5ms, iters:33, tasks:6}, scan_detail: {total_process_keys: 1696, total_process_keys_size: 122112, total_keys: 1699, get_snapshot_time: 739.1µs, rocksdb: {key_skipped_count: 1696, block: {cache_hit_count: 15}}}                                | keep order:false                                                                                                                                                                                                       | N/A       | N/A     |
|   └─HashJoin_30(Probe)                   | 100000000.00 | 0       | root      |                               | time:9.21ms, loops:1, build_hash_table:{total:9.04ms, fetch:9.04ms, build:0s}                                                                                                                                                                                                                               | CARTESIAN inner join                                                                                                                                                                                                   | 0 Bytes   | 0 Bytes |
|     ├─TableReader_43(Build)              | 10000.00     | 1024    | root      |                               | time:9.02ms, loops:1, cop_task: {num: 3, max: 4.88ms, min: 1.05ms, avg: 2.84ms, p95: 4.88ms, max_proc_keys: 992, p95_proc_keys: 992, rpc_num: 3, rpc_time: 8.49ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}                                                                                     | data:TableFullScan_42                                                                                                                                                                                                  | 183.1 KB  | N/A     |
|     │ └─TableFullScan_42                 | 10000.00     | 1696    | cop[tikv] | table:t3                      | tikv_task:{proc max:1ms, min:0s, avg: 333.3µs, p80:1ms, p95:1ms, iters:12, tasks:3}, scan_detail: {total_process_keys: 1696, total_process_keys_size: 122112, total_keys: 1699, get_snapshot_time: 1.1ms, rocksdb: {key_skipped_count: 1696, block: {cache_hit_count: 9}}}                                  | keep order:false                                                                                                                                                                                                       | N/A       | N/A     |
|     └─HashJoin_33(Probe)                 | 10000.00     | 0       | root      |                               | time:6.13ms, loops:1, build_hash_table:{total:2.06ms, fetch:2.06ms, build:0s}                                                                                                                                                                                                                               | CARTESIAN inner join                                                                                                                                                                                                   | 0 Bytes   | 0 Bytes |
|       ├─IndexLookUp_39(Build)            | 1.00         | 0       | root      |                               | time:2.03ms, loops:1                                                                                                                                                                                                                                                                                        |                                                                                                                                                                                                                        | 213 Bytes | N/A     |
|       │ ├─IndexRangeScan_37(Build)       | 1.00         | 0       | cop[tikv] | table:t2, index:t2_col1(col1) | time:1.93ms, loops:1, cop_task: {num: 1, max: 1.87ms, proc_keys: 0, rpc_num: 1, rpc_time: 1.86ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, tikv_task:{time:0s, loops:1}, scan_detail: {total_keys: 1, get_snapshot_time: 960.3µs, rocksdb: {block: {cache_hit_count: 5}}}                      | range:["AAAA","AAAA"], keep order:false                                                                                                                                                                                | N/A       | N/A     |
|       │ └─TableRowIDScan_38(Probe)       | 1.00         | 0       | cop[tikv] | table:t2                      |                                                                                                                                                                                                                                                                                                             | keep order:false                                                                                                                                                                                                       | N/A       | N/A     |
|       └─TableReader_41(Probe)            | 10000.00     | 1024    | root      |                               | time:5.99ms, loops:1, cop_task: {num: 3, max: 2.63ms, min: 1.77ms, avg: 2.14ms, p95: 2.63ms, max_proc_keys: 992, p95_proc_keys: 992, rpc_num: 3, rpc_time: 6.36ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}                                                                                     | data:TableFullScan_40                                                                                                                                                                                                  | 183.1 KB  | N/A     |
|         └─TableFullScan_40               | 10000.00     | 1696    | cop[tikv] | table:t4                      | tikv_task:{proc max:1ms, min:0s, avg: 666.7µs, p80:1ms, p95:1ms, iters:12, tasks:3}, scan_detail: {total_process_keys: 1696, total_process_keys_size: 122112, total_keys: 1699, get_snapshot_time: 735.5µs, rocksdb: {delete_skipped_count: 7767, key_skipped_count: 9463, block: {cache_hit_count: 78}}}   | keep order:false                                                                                                                                                                                                       | N/A       | N/A     |
+------------------------------------------+--------------+---------+-----------+-------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
13 rows in set, 1 warning (0.02 sec)
MySQL [test]> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                             |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1815 | There are no matching table names for (t2) in optimizer hint /*+ LEADING(t2, t4, t3, t1) */. Maybe you can use the table alias name |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL [test]> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v6.5.9
Edition: Community
Git Commit Hash: 9815b4534e22d5db87ad38347546071d27c58431
Git Branch: heads/refs/tags/v6.5.9
UTC Build Time: 2024-04-02 10:59:21
GoVersion: go1.19.13
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv
1 row in set (0.00 sec)