The specified multi-table join order in the leading hint is not effective

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

Original topic: leading hint指定多表关联顺序不生效

| username: yeminhua

Why does specifying the join order with leading not take effect, while forcing the use of an index works? The warnings don’t indicate where the problem is. However, if the join order of the tables is changed, the join order does change, indicating that it should be adjustable. But I don’t understand why leading doesn’t take effect. Is there a restriction on outer joins?

| username: zhanggame1 | Original post link

Have you tried using the index without forcing it?

| username: yeminhua | Original post link

Forcing the use of an index is just for testing to ensure it’s not an issue with the environment that prevents using hints. Without hints, the index definitely won’t be used here. These are actually empty tables.

| username: MrSylar | Original post link

The LEADING hint will become invalid in the following situations:

  • Multiple LEADING hints are specified
  • The table name specified in the LEADING hint does not exist
  • Duplicate table names are specified in the LEADING hint
  • The optimizer cannot join tables in the order specified by the LEADING hint
  • A straight_join() hint already exists
  • The query contains an outer join
  • It is used simultaneously with hints for selecting join algorithms (i.e., MERGE_JOIN, INL_JOIN, INL_HASH_JOIN, HASH_JOIN)

When any of the above invalid situations occur, a warning will be output.

| username: redgame | Original post link

The optimizer might consider other orders to be more optimal, so it chose a different join order.

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

When using outer joins, the join order depends on the order of your outer joins, and leading cannot specify it.

| username: yeminhua | Original post link

I’m using version 6.5.1. The documentation states that “the query will only fail if it contains an outer join and also specifies a Cartesian product.” Could it be that this issue hasn’t actually been fixed?

| username: yeminhua | Original post link

My version is 6.5.1. The documentation for versions above 6.5 states that it will only be invalid if “the query statement contains an outer join and simultaneously specifies a Cartesian product.”

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

I tested it in version 6.6, and it still doesn’t work.

| username: h5n1 | Original post link

Shouldn’t a left join be associated in the order it is written?

| username: MrSylar | Original post link

I tested the leading function in version 7.1 and it works.

| username: h5n1 | Original post link

The image is not available for translation. Please provide the text content directly.

| username: yeminhua | Original post link

Thank you, it was indeed this parameter causing the issue. It was upgraded from 6.1, and it was originally set to off. Now, changing it to on works.

However, this is an ideal situation in the test environment. In actual production, when there are many associated tables, it still doesn’t work. In the end, I still have to arrange them in the desired order and use STRAIGHT_JOIN() to fix the order.

| username: system | Original post link

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