In TiDB v4.0.9, the join order of the driving table cannot be changed through hints

Translated
1

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

Original topic: tidb v4.0.9中的join通过hints不能改变驱动表的顺序

| username: ablewang_xiaobo

[TiDB Usage Environment] Production Environment

[Overview] Scenario + Problem Overview
In TiDB v4.0.9, there are two tables being joined. The sizes of the two tables are 300,000 (trade_retail_bill) and 700,000 (trade_retail_bill_line) respectively. When performing the join, the larger table is always used as the driving table. Using straight_join did not change the order, but after using INL_join (trade_retail_bill) hint, the join order changed, but the returned values were still based on the larger table.

===== Original Execution Plan

image
image1187×322 18.6 KB

=== Using straight_join

image
image1193×322 17.9 KB

== Execution Plan after Adding Hints

image
image1234×331 18.2 KB

[Problem] Current Issue Encountered

[Business Impact] When joining the two tables, the returned data is always based on the larger table, resulting in a large amount of data.

[TiDB Version] V4.0.9

2
| username: hey-hoho | Original post link

Try using the hint hash join.

3
| username: ablewang_xiaobo | Original post link

Same as states 1 and 2.

4
| username: ablewang_xiaobo | Original post link

Both tables have already been analyzed.

5
| username: ablewang_xiaobo | Original post link

I checked the health status of the two tables, one is 99 and the other is 97, which seems fine.

6
| username: h5n1 | Original post link

Could you please provide the SQL table structure?

7
| username: ablewang_xiaobo | Original post link

==Table Structure
CREATE TABLE trade_retail_bill (
id bigint(20) NOT NULL,
bill_no varchar(32) COLLATE utf8_general_ci DEFAULT NULL,
bill_status varchar(2) COLLATE utf8_general_ci DEFAULT NULL,

PRIMARY KEY (id),
UNIQUE KEY bill_no (bill_no),
KEY idx_retail_bill_no (bill_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

CREATE TABLE trade_retail_bill_line (
id bigint(20) NOT NULL,
bill_id bigint(20) DEFAULT NULL,

PRIMARY KEY (id),
KEY idx_retail_line_sku_id (sku_id),
KEY idx_retail_line_goods_id (goods_id),
KEY idx_retail_line_bill_id (bill_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

==Query Statement
SELECT *****
FROM
“trade_retail_bill” “trade_retail_bill”,
“trade_retail_bill_line” “trade_retail_bill_line”
WHERE
“trade_retail_bill”.“bill_status” IN (‘05’)
AND (
“trade_retail_bill”.“id” = “trade_retail_bill_line”.“bill_id”
);

8
| username: h5n1 | Original post link

The parameter new_collations_enabled_on_first_bootstrap is set to what?

9
| username: ablewang_xiaobo | Original post link

True

10
| username: h5n1 | Original post link

It should be this bug. Judging by the affected version range, it was resolved after 4.0.12.

11
| username: ablewang_xiaobo | Original post link

It seems that the bug mentioned is related to the text type, while my foreign key was of the bigint type.