When tables have comparable index levels, simply swapping the order of tables can result in significant differences in execution efficiency

Translated
1

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

Original topic: 表量级相当索引都有的情况，简单的调换表的顺序，执行效率差异很大。

| username: liuzx8888

[TiDB Usage Environment] Test
[TiDB Version] 6.3.0
[Encountered Problem: Phenomenon and Impact]
The execution plans differ greatly when the order of two tables with similar amounts of data is swapped. How should this be adjusted:

  • JZ_MJZ_JZJL2 Clustered Index (jzxh, jzlb, yljgdm) Table Data Volume: 17,499,037 rows
  • HZ_JZJBXX Clustered Index (jzxh, jzlb, yljgdm) Table Data Volume: 17,147,543 rows
    Simply swapping the order,
    Join statement
    query1:
   select ....
    from JZ_MJZ_JZJL2   a
    inner  join HZ_JZJBXX  hz  on    a.jzxh=hz.jzxh and a.jzlb=hz.jzlb   and a.yljgdm=hz.yljgdm 
    where  a.hzid= '728615'

query2:

   select ....
    from  HZ_JZJBXX      a
    inner  join JZ_MJZ_JZJL2 hz  on    a.jzxh=hz.jzxh and a.jzlb=hz.jzlb   and a.yljgdm=hz.yljgdm 
    where  a.hzid= '728615'

Table structure: JZ_MJZ_JZJL2

+------------------------+---------------+------+------+----------------------+-------+
| Field                  | Type          | Null | Key  | Default              | Extra |
+------------------------+---------------+------+------+----------------------+-------+
| bxh                    | bigint(20)    | NO   |      | NULL                 |       |
| yljgdm                 | varchar(20)   | NO   | PRI  | NULL                 |       |
| yljgmc                 | varchar(50)   | YES  |      | NULL                 |       |
| jzxh                   | varchar(36)   | NO   | PRI  | NULL                 |       |
| jzlb                   | varchar(4)    | NO   | PRI  | NULL                 |       |
| hzid                   | varchar(36)   | YES  | MUL  | NULL                 |       |
| ghjslsh                | varchar(36)   | YES  |      | NULL                 |       |
| ghsj                   | datetime(3)   | YES  |      | NULL                 |       |
| cfzbz                  | varchar(4)    | YES  |      | NULL                 |       |
| yyghbz                 | varchar(4)    | YES  |      | NULL                 |       |
| mzyyxh                 | varchar(36)   | YES  |      | NULL                 |       |
| ghksdm                 | varchar(20)   | YES  |      | NULL                 |       |
| ghksmc                 | varchar(50)   | YES  |      | NULL                 |       |
| ghysdm                 | varchar(20)   | YES  |      | NULL                 |       |
| ghysmc                 | varchar(50)   | YES  |      | NULL                 |       |
| ghfsdm                 | varchar(20)   | YES  |      | NULL                 |       |
| ghfsmc                 | varchar(50)   | YES  |      | NULL                 |       |
| ghlbdm                 | varchar(20)   | YES  |      | NULL                 |       |
| ghlbmc                 | varchar(50)   | YES  |      | NULL                 |       |
| txmzbz                 | varchar(4)    | YES  |      | NULL                 |       |
| ghjlzt                 | varchar(4)    | YES  |      | NULL                 |       |
| tjzxh                  | varchar(36)   | YES  |      | NULL                 |       |
| thsj                   | datetime(3)   | YES  |      | NULL                 |       |
| ghczydm                | varchar(20)   | YES  |      | NULL                 |       |
| ghczymc                | varchar(50)   | YES  |      | NULL                 |       |
| ghhx                   | varchar(20)   | YES  |      | NULL                 |       |
| jzztdm                 | varchar(20)   | YES  |      | NULL                 |       |
| jzztmc                 | varchar(50)   | YES  |      | NULL                 |       |
| zjscjzsj               | datetime(3)   | YES  |      | NULL                 |       |
| zjecjzsj               | datetime(3)   | YES  |      | NULL                 |       |
| zjzhjzsj               | datetime(3)   | YES  |      | NULL                 |       |
| jzksdm                 | varchar(20)   | YES  |      | NULL                 |       |
| jzksmc                 | varchar(50)   | YES  |      | NULL                 |       |
| scjzysdm               | varchar(20)   | YES  |      | NULL                 |       |
| scjzysmc               | varchar(50)   | YES  |      | NULL                 |       |
| zhjzysdm               | varchar(20)   | YES  |      | NULL                 |       |
| zhjzysmc               | varchar(50)   | YES  |      | NULL                 |       |
| sfzlqj                 | varchar(4)    | YES  |      | NULL                 |       |
| sfqjcg                 | varchar(4)    | YES  |      | NULL                 |       |
| sfsw                   | varchar(4)    | YES  |      | NULL                 |       |
| termflag               | varchar(4)    | YES  |      | NULL                 |       |
| termfailedcolumn       | varchar(500)  | YES  |      | NULL                 |       |
| termfailedmessage      | varchar(5000) | YES  |      | NULL                 |       |
| termsuccesscolumn      | varchar(500)  | YES  |      | NULL                 |       |
| source                 | varchar(50)   | YES  |      | NULL                 |       |
| source_tablename       | varchar(50)   | YES  |      | NULL                 |       |
| source_key             | varchar(500)  | YES  |      | NULL                 |       |
| source_keyvalue        | varchar(500)  | YES  |      | NULL                 |       |
| isdelete               | varchar(4)    | YES  |      | NULL                 |       |
| source_lastupdate      | datetime(3)   | YES  |      | NULL                 |       |
| destination_lastupdate | datetime(3)   | YES  |      | NULL                 |       |
| timestp                | varbinary(50) | YES  |      | NULL                 |       |
| pch                    | varchar(20)   | YES  |      | NULL                 |       |
| title_code             | varchar(50)   | YES  |      | NULL                 |       |
| yydm                   | varchar(50)   | YES  |      | NULL                 |       |
| ghf                    | decimal(5,2)  | YES  |      | NULL                 |       |
| fylb                   | varchar(32)   | YES  |      | NULL                 |       |
| ghrq                   | datetime(3)   | YES  |      | NULL                 |       |
| swsj                   | datetime(3)   | YES  | CURRENT_TIMESTAMP(3) |       |
| trial674               | char(1)       | YES  |      | NULL                 |       |
+------------------------+---------------+------+------+----------------------+-------+

Table structure: HZ_JZJBXX

+------------------------+--------------+------+------+---------+-------+
| Field                  | Type         | Null | Key  | Default | Extra |
+------------------------+--------------+------+------+---------+-------+
| bxh                    | bigint(20)   | YES  |      | NULL    |       |
| yljgdm                 | varchar(50)  | NO   | PRI  | NULL    |       |
| yljgmc                 | varchar(50)  | NO   |      | NULL    |       |
| jzxh                   | varchar(36)  | NO   | PRI  | NULL    |       |
| jzlb                   | varchar(4)   | NO   | PRI  | NULL    |       |
| hzid                   | varchar(36)  | YES  | MUL  | NULL    |       |
| blh                    | varchar(36)  | YES  | MUL  | NULL    |       |
| hzxm                   | varchar(128) | YES  | MUL  | NULL    |       |
| jsm                    | varchar(255) | YES  |      | NULL    |       |
| hzxbdm                 | varchar(128) | YES  |      | NULL    |       |
| hzxbmc                 | varchar(128) | YES  |      | NULL    |       |
| hzcsrq                 | date         | YES  |      | NULL    |       |
| hzsfzh                 | varchar(36)  | YES  | MUL  | NULL    |       |
| hyzkdm                 | varchar(32)  | YES  |      | NULL    |       |
| hyzkmc                 | varchar(128) | YES  |      | NULL    |       |
| mzdm                   | varchar(32)  | YES  |      | NULL    |       |
| mzmc                   | varchar(128) | YES  |      | NULL    |       |
| gjdm                   | varchar(32)  | YES  |      | NULL    |       |
| gjmc                   | varchar(128) | YES  |      | NULL    |       |
| zydm                   | varchar(32)  | YES  |      | NULL    |       |
| zymc                   | varchar(128) | YES  |      | NULL    |       |
| whcddm                 | varchar(32)  | YES  |      | NULL    |       |
| whcdmc                 | varchar(128) | YES  |      | NULL    |       |
| hzgzdwmc               | varchar(128) | YES  |      | NULL    |       |
| hzgddh                 | varchar(32)  | YES  |      | NULL    |       |
| hzyddh                 | varchar(32)  | YES  |      | NULL    |       |
| hzemail                | varchar(50)  | YES  |      | NULL    |       |
| ynkh                   | varchar(36)  | YES  | MUL  | NULL    |       |
| ybkh                   | varchar(36)  | YES  | MUL  | NULL    |       |
| djsj                   | datetime     | YES  |      | NULL    |       |
| djczydm                | varchar(32)  | YES  |      | NULL    |       |
| djczymc                | varchar(128) | YES  |      | NULL    |       |
| jlzt                   | varchar(4)   | YES  |      | NULL    |       |
| termflag               | varchar(4)   | YES  |      | NULL    |       |
| termfailedcolumn       | text         | YES  |      | NULL    |       |
| termfailedmessage      | text         | YES  |      | NULL    |       |
| termsuccesscolumn      | text         | YES  |      | NULL    |       |
| source                 | varchar(50)  | YES  |      | NULL    |       |
| source_tablename       | varchar(50)  | YES  |      | NULL    |       |
| source_key             | text         | YES  |      | NULL    |       |
| source_keyvalue        | text         | YES  |      | NULL    |       |
| isdelete               | varchar(4)   | YES  |      | NULL    |       |
| source_lastupdate      | datetime     | YES  |      | NULL    |       |
| destination_lastupdate | datetime     | YES  |      | NULL    |       |
| timestp                | longblob     | NO   |      | NULL    |       |
| pch                    | varchar(32)  | YES  |      | NULL    |       |
| nl                     | int(11)      | YES  |      | NULL    |       |
| jzdz                   | text         | YES  |      | NULL    |       |
| yydm                   | varchar(50)  | YES  |      | NULL    |       |
| jzcs                   | int(11)      | YES  |      | NULL    |       |
+------------------------+--------------+------+------+---------+-------+

[Resource Configuration]

image
image1025×379 15.1 KB

image
image956×313 12.1 KB

[Attachments: Screenshots/Logs/Monitoring]
Execution plan of query1

image
image997×312 13.9 KB

Execution plan of query2

image
image997×314 11.4 KB

From the images, it is clear that one uses the slowest table scan method, while the other directly uses the index and pushes down the records. The table sizes are similar, and the index matches the join keys perfectly. Why is the index invalid for query1?

Tried ANALYZE TABLE operation and forced index usage, but the speed is still very slow. Can any expert explain why?

2
| username: h5n1 | Original post link

Please provide the table structures of both tables and try using the INL_JOIN (inner table) HINT.

3
| username: buddyyuan | Original post link

Use PLAN REPLAYER to collect some information and upload it.

4
| username: ddhe9527 | Original post link

The two SQL statements you provided are completely different. You only changed the table but didn’t change the alias. In the final WHERE condition, query1 is “WHERE JZ_MJZ_JZJL2.hzid=XXXX” and query2 is “WHERE HZ_JZJBXX.hzid=XXX”. The target tables for the WHERE conditions in the two SQL statements are different.

5
| username: xfworld | Original post link

You read it very carefully~ :+1: :smiley_cat:

6
| username: liuzx8888 | Original post link

Both tables have primary keys, and the foreign key is a clustered index. The hzid has an index, but one query doesn’t use the index while the other does. Does this have a significant relationship with the WHERE clause? Theoretically, shouldn’t both queries use the index? One of them is performing a table scan directly.

7
| username: liuzx8888 | Original post link

I have added the table results, please take a look again.

8
| username: ddhe9527 | Original post link

The table on which the WHERE condition is applied will affect the JOIN method and the order of the driving table.

9
| username: liuzx8888 | Original post link

PLAN REPLAYER has been uploaded:
query1.zip (315.4 KB)
query2.zip (315.4 KB)

10
| username: h5n1 | Original post link

Although your two SQL aliases are different tables, the structures of the two tables are similar, and the primary keys are the same. It is likely an optimizer defect. I have encountered this before, where changing the order of the associated tables resulted in a full table scan in the execution plan.

11
| username: 胡杨树旁 | Original post link

In theory, if there is an index after the where condition, it should use the index, right?

12
| username: xfworld | Original post link

This is determined by the Cost-Based Optimizer (CBO). Whether it’s an index or a table scan, there will be corresponding statistics and data to support it. If the statistics are inaccurate, it’s hard to say.

13
| username: buddyyuan | Original post link

Please execute and check the output.

14
| username: buddyyuan | Original post link

I see that Lower_Bound and Upper_Bound have one set of data in half-width characters and another set in full-width characters. Check if that’s the case on your end.

15
| username: liuzx8888 | Original post link

Hmm

16
| username: buddyyuan | Original post link

Are both the Lower_Bound and Upper_Bound of this column hz_jzjbxx empty?

17
| username: 胡杨树旁 | Original post link

I would like to ask if this SQL is querying the status of the statistics buckets? What is the difference between this and show stats_histograms? In a version 4.0 database, the columns of the table in show stats_histograms are not fully displayed, and even after recollecting the statistics, they are still not fully displayed. I am not sure if this is a version issue or something else.

18
| username: liuzx8888 | Original post link

All 255 bucket_ids are empty.

19
| username: buddyyuan | Original post link

The SHOW STATS_HISTOGRAMS command can display the distinct count of each column, also known as selectivity. Through this, you can evaluate whether to create an index on this column.

The SHOW STATS_BUCKETS command is used to view the information of each bucket in the histogram. This should be the actual information of the histogram.

20
| username: buddyyuan | Original post link

Try collecting the statistics for this table again.