Can SQL hints specify the join order for index join or hash join?

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

Original topic: sql hint 能不能指定index join 或者hash join的连接顺序

| username: Raymond

Dear teachers, can SQL hints specify the join order for index join or hash join? These tables are from TPC-H.

For example, I want to specify that table ‘a’ is the driving table and table ‘b’ is the driven table, but in the execution plan, it seems that ‘b’ is still the driving table.

mysql> explain analyze select /*+ INL_JOIN(a,b) */ a.O_ORDERKEY, a.O_CUSTKEY, b.C_NAME from orders a join customer b on a.O_CUSTKEY = b.C_CUSTKEY;
+-----------------------------+-------------+----------+-----------+-------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id                          | estRows     | actRows  | task      | access object                             | execution info                                                                                                                                                                                                                                                                                                 | operator info                                                                                                                                                       | memory  | disk |
+-----------------------------+-------------+----------+-----------+-------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| IndexJoin_19                | 29955968.00 | 29955968 | root      |                                           | time:57.4s, loops:29255, inner:{total:4m38.7s, concurrency:5, task:126, construct:1.66s, fetch:4m33.3s, build:3.77s}, probe:4.9s                                                                                                                                                                               | inner join, inner:IndexReader_18, outer key:tpch.customer.c_custkey, inner key:tpch.orders.o_custkey, equal cond:eq(tpch.customer.c_custkey, tpch.orders.o_custkey) | 56.5 MB | N/A  |
| ├─TableReader_31(Build)     | 3000000.00  | 3000000  | root      |                                           | time:85.1ms, loops:2941, cop_task: {num: 116, max: 554.8ms, min: 2.49ms, avg: 87.6ms, p95: 250.6ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 4.37s, tot_wait: 214ms, rpc_num: 116, rpc_time: 10.2s, copr_cache_hit_ratio: 0.15, distsql_concurrency: 15}                                          | data:TableFullScan_30                                                                                                                                               | 13.0 MB | N/A  |
| │ └─TableFullScan_30        | 3000000.00  | 3000000  | cop[tikv] | table:b                                   | tikv_task:{proc max:342ms, min:0s, avg: 36.3ms, p80:53ms, p95:150ms, iters:3387, tasks:116}, scan_detail: {total_process_keys: 2876640, total_process_keys_size: 585339241, total_keys: 2876739, get_snapshot_time: 45.2ms, rocksdb: {key_skipped_count: 2876640, block: {cache_hit_count: 10142}}}            | keep order:false                                                                                                                                                    | N/A     | N/A  |
| └─IndexReader_18(Probe)     | 29955968.00 | 29955968 | root      |                                           | time:4m29.6s, loops:29678, cop_task: {num: 2633, max: 957.2ms, min: 1.4ms, avg: 131.5ms, p95: 468.1ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 3m14.7s, tot_wait: 26.4s, rpc_num: 2633, rpc_time: 5m46.1s, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}                                  | index:IndexRangeScan_17                                                                                                                                             | 17.7 KB | N/A  |
|   └─IndexRangeScan_17       | 29955968.00 | 29955968 | cop[tikv] | table:a, index:index_o_custkey(O_CUSTKEY) | tikv_task:{proc max:831ms, min:0s, avg: 73.3ms, p80:117ms, p95:377ms, iters:39544, tasks:2633}, scan_detail: {total_process_keys: 29955968, total_process_keys_size: 1377974528, total_keys: 32958332, get_snapshot_time: 370.6ms, rocksdb: {key_skipped_count: 29955968, block: {cache_hit_count: 18033644}}} | range: decided by [eq(tpch.orders.o_custkey, tpch.customer.c_custkey)], keep order:false                                                                            | N/A     | N/A  |
+-----------------------------+-------------+----------+-----------+-------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
5 rows in set (57.38 sec)

Table structure is as follows:

mysql> show create table customer;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                    |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| customer | CREATE TABLE `customer` (
  `C_CUSTKEY` bigint(20) NOT NULL,
  `C_NAME` varchar(25) NOT NULL,
  `C_ADDRESS` varchar(40) NOT NULL,
  `C_NATIONKEY` bigint(20) NOT NULL,
  `C_PHONE` char(15) NOT NULL,
  `C_ACCTBAL` decimal(15,2) NOT NULL,
  `C_MKTSEGMENT` char(10) NOT NULL,
  `C_COMMENT` varchar(117) NOT NULL,
  PRIMARY KEY (`C_CUSTKEY`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table orders;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| orders | CREATE TABLE `orders` (
  `O_ORDERKEY` bigint(20) NOT NULL,
  `O_CUSTKEY` bigint(20) NOT NULL,
  `O_ORDERSTATUS` char(1) NOT NULL,
  `O_TOTALPRICE` decimal(15,2) NOT NULL,
  `O_ORDERDATE` date NOT NULL,
  `O_ORDERPRIORITY` char(15) NOT NULL,
  `O_CLERK` char(15) NOT NULL,
  `O_SHIPPRIORITY` bigint(20) NOT NULL,
  `O_COMMENT` varchar(79) NOT NULL,
  PRIMARY KEY (`O_ORDERKEY`) /*T![clustered_index] CLUSTERED */,
  KEY `index_o_custkey` (`O_CUSTKEY`),
  KEY `O_TOTALPRICE` (`O_TOTALPRICE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
  1. When using hash join, can we specify one table to build the hash table, for example, specifying table ‘a’ to build the hash table?
explain analyze select /*+ HASH_JOIN(a,b) */ a.O_ORDERKEY, a.O_CUSTKEY, b.C_NAME from orders a join customer b on a.O_CUSTKEY = b.C_CUSTKEY;
| username: 昵称想不起来了 | Original post link

SELECT /*+ HASH_JOIN_BUILD(t1) */ * FROM t1, t2 WHERE t1.id = t2.id;
HASH_JOIN_BUILD(t1_name [, tl_name …])

| username: Jellybean | Original post link

Yes, there are parameters that allow users to specify the join order according to their business needs.

| username: xfworld | Original post link

Is there any experience or documentation that can be shared? :blush:

| username: Raymond | Original post link

Is it this kind of straight_join?

| username: Jellybean | Original post link

STRAIGHT_JOIN will force the optimizer to perform the join query in the order of the tables used in the FROM clause. When the join order chosen by the optimizer is not optimal, this syntax can be used to speed up query execution.

You can check if this is the expected feature.

| username: Jellybean | Original post link

I see from the official documentation that STRAIGHT_JOIN can be used to force the join order in a query.

| username: xfworld | Original post link

Such a high version, I guess we’ll have to wait for the next LTS… :rofl: :rofl: :rofl:

| username: Jellybean | Original post link

The version in the link is just the one that comes with the URL I posted, not the version of this feature. I remember it was supported a long time ago.
Join Reorder 算法简介 | PingCAP 归档文档站 On the last line of this page.

| username: Raymond | Original post link

Assuming you need to make table b act as the inner table in an index join, you can use this syntax:
/*+ INL_JOIN(b) */
It’s also mentioned in the official documentation.