Can the SQL join method be modified? For example, changing hash join to index join

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

Original topic: 能修改SQL的join方式吗? 比如将hash join改为index join

| username: DBRE

[TiDB Usage Environment] Production Environment
[TiDB Version] 5.2.3

| username: TiDBer_JUi6UvZm | Original post link

You can refer to this for modifications and use the query plan to check: 用 EXPLAIN 查看 JOIN 查询的执行计划 | PingCAP 文档中心

| username: DBRE | Original post link

I tried it, but it still doesn’t work. The execution plan is as follows:

| username: DBRE | Original post link

This SQL execution takes about 0.8 seconds. When the where condition t1.deleted=0 is removed, the query becomes much faster, and the execution plan changes as shown below:

Why does the query become faster when t1.deleted=0 is removed? Are there any optimization solutions?

| username: zhanggame1 | Original post link

The MySQL client removes comments by default; you need to add --comments.

| username: caiyfc | Original post link

  1. After you added the hint, a warning appeared:
  2. If the hint is not effective, you can check here: Optimizer Hints | PingCAP Documentation Center
| username: DBRE | Original post link

Specified --comments, but still getting a warning.

| username: DBRE | Original post link

mysql> show warnings;
±--------±-----±---------------------------------------------------------------------------------+
| Level | Code | Message |
±--------±-----±---------------------------------------------------------------------------------+
| Warning | 1815 | Optimizer Hint /*+ INL_JOIN(t, t1) / or /+ TIDB_INLJ(t, t1) */ is inapplicable |
±--------±-----±---------------------------------------------------------------------------------+
1 row in set (0.00 sec)

| username: caiyfc | Original post link

Try it like this: INL_JOIN(t1)

| username: DBRE | Original post link

The same, the join method has not changed.

| username: caiyfc | Original post link

Check the table structure, whether the character set collation is the same, whether the associated fields are the same, and whether there are corresponding indexes.

| username: DBRE | Original post link

There is an index, and the character set is the same. When the condition “t1.deleted=0” is removed from the WHERE clause, the query becomes very fast, and the execution plan changes as follows:

image

| username: caiyfc | Original post link

This is using index join. I’m not sure why the condition t1.deleted=0 prevents it from using index join.

| username: paulli | Original post link

Removing t1.delete=0 changes the semantics, right? Besides the collation of the join columns needing to be consistent, not using index join could be due to inaccurate data estimation for t1.

| username: QH琉璃 | Original post link

Waiting for the expert’s answer.

| username: 哈喽沃德 | Original post link

What percentage of the total table does the data with deleted=0 account for?

| username: DBRE | Original post link

The “deleted=1” in table t1 accounts for nearly 100% of the entire table.

| username: Jasper | Original post link

You can try creating a composite index on t1 (newHouseUuid, deleted).

| username: DBRE | Original post link

A composite index still doesn’t work.
The t1.deleted field is of bit type. After changing t1.deleted to tinyint type, the final test shows an index scan. It’s unclear why this happens.

| username: zhh_912 | Original post link

Check the table structure, then look at the SQL.