Using Hints to Bind Execution Plan is Ineffective

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

Original topic: 使用hint来绑定执行计划不生效

| username: terry0219

【TiDB Usage Environment】Testing/PoC
【TiDB Version】7.5.0
I want to change the hashJoin operator to indexJoin. After using explain SELECT /*+ INL_JOIN(table1, table2) */, the execution plan still uses hashJoin. Does anyone know the reason?

| username: buddyyuan | Original post link

Try adding the parameter -c to the MySQL client to see if there are any warnings.

| username: terry0219 | Original post link

There are logs:
Warning | 1815 | Optimizer Hint /*+ INL_JOIN(table1, table2) / or /+ TIDB_INLJ(table1, table2) */ is inapplicable

| username: buddyyuan | Original post link

There are definitely limitations, it won’t work.
The most classic situation is that you don’t have an index, so it can’t handle this hint.

| username: Soysauce520 | Original post link

If the table name has an alias, use the alias.

| username: terry0219 | Original post link

I checked the fields of the joined tables, one is the primary key id, and the other is a regular index.

| username: terry0219 | Original post link

Did not use an alias.

| username: caiyfc | Original post link

Common Hint Ineffectiveness Troubleshooting Check this first, it might also be caused by implicit conversion leading to ineffectiveness.

| username: forever | Original post link

Could you please share your original SQL?

| username: tidb菜鸟一只 | Original post link

Please share the execution plan.

| username: terry0219 | Original post link

The image is not visible. Please provide the text you need translated.

| username: terry0219 | Original post link

The SQL query SELECT COUNT(id) amount FROM table1 WHERE b_id=241 AND status=1 AND ((look_limit=1 ) OR user_id=xxx OR (look_limit=3 AND id IN ( SELECT r_id FROM table2 WHERE x_id=241 AND user_id=xx AND status = 1 ) )) AND app=1; has been anonymized.

| username: 江湖故人 | Original post link

The collation might be incompatible. Check the COLLATE of the tables.

show create table table1;
show create table table2;
| username: terry0219 | Original post link

I checked that the COLLATE=utf8mb4_bin of both tables is the same.

| username: gary | Original post link

What does “show warnings;” report?

| username: buddyyuan | Original post link

I tried it, but it doesn’t seem to work. The or contains a subquery, so I suggest changing or to union.

mysql> explain select * from t1 where (t1.b=0 or t1.a in (select a from t2 where t2.b=2));
+----------------------------------+----------+-----------+---------------+---------------------------------------------------------------------+
| id                               | estRows  | task      | access object | operator info                                                       |
+----------------------------------+----------+-----------+---------------+---------------------------------------------------------------------+
| Projection_8                     | 8000.00  | root      |               | test.t1.a, test.t1.b                                                |
| └─Selection_9                    | 8000.00  | root      |               | or(eq(test.t1.b, 0), Column#7)                                      |
|   └─HashJoin_10                  | 10000.00 | root      |               | CARTESIAN left outer semi join, other cond:eq(test.t1.a, test.t2.a) |
|     ├─TableReader_15(Build)      | 10.00    | root      |               | data:Selection_14                                                   |
|     │ └─Selection_14             | 10.00    | cop[tikv] |               | eq(test.t2.b, 2)                                                    |
|     │   └─TableFullScan_13       | 10000.00 | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                      |
|     └─TableReader_12(Probe)      | 10000.00 | root      |               | data:TableFullScan_11                                               |
|       └─TableFullScan_11         | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                      |
+----------------------------------+----------+-----------+---------------+---------------------------------------------------------------------+
8 rows in set (0.00 sec)

mysql> explain select /*+inl_join(t1)*/ * from t1 where (t1.b=0 or t1.a in (select a from t2 where t2.b=2));
+----------------------------------+----------+-----------+---------------+---------------------------------------------------------------------+
| id                               | estRows  | task      | access object | operator info                                                       |
+----------------------------------+----------+-----------+---------------+---------------------------------------------------------------------+
| Projection_8                     | 8000.00  | root      |               | test.t1.a, test.t1.b                                                |
| └─Selection_9                    | 8000.00  | root      |               | or(eq(test.t1.b, 0), Column#7)                                      |
|   └─HashJoin_10                  | 10000.00 | root      |               | CARTESIAN left outer semi join, other cond:eq(test.t1.a, test.t2.a) |
|     ├─TableReader_15(Build)      | 10.00    | root      |               | data:Selection_14                                                   |
|     │ └─Selection_14             | 10.00    | cop[tikv] |               | eq(test.t2.b, 2)                                                    |
|     │   └─TableFullScan_13       | 10000.00 | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                      |
|     └─TableReader_12(Probe)      | 10000.00 | root      |               | data:TableFullScan_11                                               |
|       └─TableFullScan_11         | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                      |
+----------------------------------+----------+-----------+---------------+---------------------------------------------------------------------+
8 rows in set, 1 warning (0.00 sec)

mysql> explain select * from t1 where t1.b=0
    -> union
    -> select /*+inl_join(t1)*/ * from t1 where t1.a in (select a from t2 where t2.b=2);
+--------------------------------------+----------+-----------+----------------------+-----------------------------------------------------------------------------------------------------------------+
| id                                   | estRows  | task      | access object        | operator info                                                                                                   |
+--------------------------------------+----------+-----------+----------------------+-----------------------------------------------------------------------------------------------------------------+
| HashAgg_16                           | 17.99    | root      |                      | group by:Column#10, Column#11, funcs:firstrow(Column#10)->Column#10, funcs:firstrow(Column#11)->Column#11       |
| └─Union_17                           | 19.99    | root      |                      |                                                                                                                 |
|   ├─TableReader_21                   | 10.00    | root      |                      | data:Selection_20                                                                                               |
|   │ └─Selection_20                   | 10.00    | cop[tikv] |                      | eq(test.t1.b, 0)                                                                                                |
|   │   └─TableFullScan_19             | 10000.00 | cop[tikv] | table:t1             | keep order:false, stats:pseudo                                                                                  |
|   └─IndexJoin_28                     | 9.99     | root      |                      | inner join, inner:IndexLookUp_27, outer key:test.t2.a, inner key:test.t1.a, equal cond:eq(test.t2.a, test.t1.a) |
|     ├─HashAgg_45(Build)              | 7.99     | root      |                      | group by:test.t2.a, funcs:firstrow(test.t2.a)->test.t2.a                                                        |
|     │ └─TableReader_46               | 7.99     | root      |                      | data:HashAgg_38                                                                                                 |
|     │   └─HashAgg_38                 | 7.99     | cop[tikv] |                      | group by:test.t2.a,                                                                                             |
|     │     └─Selection_44             | 9.99     | cop[tikv] |                      | eq(test.t2.b, 2), not(isnull(test.t2.a))                                                                        |
|     │       └─TableFullScan_43       | 10000.00 | cop[tikv] | table:t2             | keep order:false, stats:pseudo                                                                                  |
|     └─IndexLookUp_27(Probe)          | 1.25     | root      |                      |                                                                                                                 |
|       ├─Selection_26(Build)          | 1.25     | cop[tikv] |                      | not(isnull(test.t1.a))                                                                                          |
|       │ └─IndexRangeScan_24          | 1.25     | cop[tikv] | table:t1, index:a(a) | range: decided by [eq(test.t1.a, test.t2.a)], keep order:false, stats:pseudo                                    |
|       └─TableRowIDScan_25(Probe)     | 1.25     | cop[tikv] | table:t1             | keep order:false, stats:pseudo                                                                                  |
+--------------------------------------+----------+-----------+----------------------+-----------------------------------------------------------------------------------------------------------------+
15 rows in set (0.00 sec)
| username: TiDBer_aaO4sU46 | Original post link

Change the connection order.

| username: terry0219 | Original post link

Thank you, I’ll give it a try.

| username: 这里介绍不了我 | Original post link

Learn a bit.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.