There is a significant performance difference when using or not using ORDER BY in a LEFT JOIN query with a COUNT operation

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

Original topic: left join连表查询count操作时,有无order by 性能差距很大

| username: 帅的掉渣

[TiDB Usage Environment] Production Environment
[TiDB Version] v7.1.1
[Reproduction Path] The left join is an unnecessary association in count queries, and the right table will be automatically pruned if there is an order by.
[Encountered Problem: Phenomenon and Impact]
Query 1: explain analyze SELECT count(0) FROM rrs_attend_partition t LEFT JOIN rrs_person rp on t.person_id = rp.id WHERE 1 = 1 AND t.project_id = ‘5678a656095428683fef6a90801b4ba’;
Query 2: explain analyze SELECT count(0) FROM rrs_attend_partition t LEFT JOIN rrs_person rp on t.person_id = rp.id WHERE 1 = 1 AND t.project_id = ‘5678a656095428683fef6a90801b4ba’ order by t.create_time;
The count query with order by is faster, which was discovered accidentally while optimizing slow SQL.
In production, there is no order by. I wanted to keep the order by for the count query, but the pagehelper pagination plugin automatically removes the order by.
Problem: The performance of the SQL with order by is significantly better than without it.
Question: Why does it depend on order by for pruning, and what is the implementation logic? How can I make the performance of Query 1 and Query 2 consistent?

mysql> explain analyze SELECT count(0) FROM rrs_attend_partition t LEFT JOIN rrs_person rp on t.person_id = rp.id WHERE 1 = 1 AND t.project_id = ‘5678a656095428683fef6a90801b4ba’;
±---------------------------------------±-----------±--------±----------±--------------------------------------------------------±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±-------------------------------------------------------------------------------------------------------±----------±--------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
±---------------------------------------±-----------±--------±----------±--------------------------------------------------------±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±-------------------------------------------------------------------------------------------------------±----------±--------+
| HashAgg_9 | 1.00 | 1 | root | | time:108.9ms, loops:2, RU:169.490572, partial_worker:{wall_time:108.894854ms, concurrency:5, task_num:0, tot_wait:543.994585ms, tot_exec:0s, tot_time:543.997945ms, max:108.807093ms, p95:108.807093ms}, final_worker:{wall_time:108.919395ms, concurrency:5, task_num:0, tot_wait:544.097297ms, tot_exec:10.183µs, tot_time:544.11254ms, max:108.829544ms, p95:108.829544ms} | funcs:count(0)->Column#69 | 6.15 KB | N/A |
| └─HashJoin_12 | 12356.53 | 0 | root | | time:108.8ms, loops:1, build_hash_table:{total:8.07ms, fetch:8.07ms, build:0s}, probe:{concurrency:5, total:542.8ms, max:108.6ms, probe:293.6ms, fetch:249.2ms} | left outer join, equal:[eq(prod_sjzt_smz.rrs_attend_partition.person_id, prod_sjzt_smz.rrs_person.id)] | 0 Bytes | 0 Bytes |
| ├─Projection_22(Build) | 12356.53 | 0 | root | | time:8.03ms, loops:1, Concurrency:5 | prod_sjzt_smz.rrs_attend_partition.project_id, prod_sjzt_smz.rrs_attend_partition.person_id | 46.4 KB | N/A |
| │ └─IndexLookUp_21 | 12356.53 | 0 | root | | time:7.96ms, loops:1 | | 296 Bytes | N/A |
| │ ├─IndexRangeScan_19(Build) | 12356.53 | 0 | cop[tikv] | table:t, index:idx_pro(project_id) | time:7.83ms, loops:1, cop_task: {num: 1, max: 7.76ms, proc_keys: 0, tot_proc: 6.05ms, tot_wait: 947.7µs, rpc_num: 1, rpc_time: 7.74ms, copr_cache_hit_ratio: 0.00, build_task_duration: 28.5µs, max_distsql_concurrency: 1}, tikv_task:{time:10ms, loops:1}, scan_detail: {total_keys: 1, get_snapshot_time: 883.2µs, rocksdb: {block: {cache_hit_count: 5, read_count: 4, read_byte: 79.4 KB, read_time: 5.44ms}}} | range:[“5678a656095428683fef6a90801b4ba”,“5678a656095428683fef6a90801b4ba”], keep order:false | N/A | N/A |
| │ └─TableRowIDScan_20(Probe) | 12356.53 | 0 | cop[tikv] | table:t | | keep order:false | N/A | N/A |
| └─IndexReader_38(Probe) | 1111826.00 | 1111827 | root | | time:45.7ms, loops:1089, cop_task: {num: 39, max: 63.6ms, min: 412.6µs, avg: 2.26ms, p95: 2.35ms, max_proc_keys: 50144, p95_proc_keys: 992, tot_proc: 63.5ms, tot_wait: 2.13ms, rpc_num: 39, rpc_time: 87.2ms, copr_cache_hit_ratio: 0.85, build_task_duration: 28µs, max_distsql_concurrency: 2} | index:IndexFullScan_37 | 3.83 MB | N/A |
| └─IndexFullScan_37 | 1111826.00 | 1111827 | cop[tikv] | table:rp, index:rrs_person_create_time_IDX(create_time) | tikv_task:{proc max:80ms, min:0s, avg: 34.1ms, p80:60ms, p95:70ms, iters:1240, tasks:39}, scan_detail: {total_process_keys: 52544, total_process_keys_size: 8932480, total_keys: 52550, get_snapshot_time: 670.5µs, rocksdb: {delete_skipped_count: 57, key_skipped_count: 52601, block: {cache_hit_count: 362}}} | keep order:false | N/A | N/A |
±---------------------------------------±-----------±--------±----------±--------------------------------------------------------±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±-------------------------------------------------------------------------------------------------------±----------±--------+
8 rows in set (0.11 sec)

mysql> explain analyze SELECT count(0) FROM rrs_attend_partition t LEFT JOIN rrs_person rp on t.person_id = rp.id WHERE 1 = 1 AND t.project_id = ‘5678a656095428683fef6a90801b4ba’ order by t.create_time;
±-------------------------------------±---------±--------±----------±-----------------------------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±-------------------------------------------------------------------------------------------------------------±----------±--------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
±-------------------------------------±---------±--------±----------±-----------------------------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±-------------------------------------------------------------------------------------------------------------±----------±--------+
| Projection_9 | 1.00 | 1 | root | | time:1.11ms, loops:2, RU:0.250587, Concurrency:OFF | Column#69 | 760 Bytes | N/A |
| └─Sort_11 | 1.00 | 1 | root | | time:1.11ms, loops:2 | prod_sjzt_smz.rrs_attend_partition.create_time | 768 Bytes | 0 Bytes |
| └─HashAgg_29 | 1.00 | 1 | root | | time:1.07ms, loops:2, partial_worker:{wall_time:1.015966ms, concurrency:5, task_num:0, tot_wait:4.92656ms, tot_exec:0s, tot_time:4.93226ms, max:991.886µs, p95:991.886µs}, final_worker:{wall_time:1.035576ms, concurrency:5, task_num:0, tot_wait:4.99453ms, tot_exec:6.07µs, tot_time:5.00488ms, max:1.008516ms, p95:1.008516ms} | funcs:count(Column#73)->Column#69, funcs:firstrow(Column#74)->prod_sjzt_smz.rrs_attend_partition.create_time | 13.6 KB | N/A |
| └─IndexLookUp_30 | 1.00 | 0 | root | | time:971.8µs, loops:1 | | 252 Bytes | N/A |
| ├─IndexRangeScan_25(Build) | 12356.53 | 0 | cop[tikv] | table:t, index:idx_pro(project_id) | time:827µs, loops:1, cop_task: {num: 1, max: 748.8µs, proc_keys: 0, tot_proc: 1.76µs, tot_wait: 125.2µs, rpc_num: 1, rpc_time: 713.2µs, copr_cache_hit_ratio: 1.00, build_task_duration: 33.6µs, max_distsql_concurrency: 1}, tikv_task:{time:10ms, loops:1}, scan_detail: {get_snapshot_time: 48.1µs, rocksdb: {block: {}}} | range:[“5678a656095428683fef6a90801b4ba”,“5678a656095428683fef6a90801b4ba”], keep order:false | N/A | N/A |
| └─HashAgg_14(Probe) | 1.00 | 0 | cop[tikv] | | | funcs:count(0)->Column#73, funcs:firstrow(prod_sjzt_smz.rrs_attend_partition.create_time)->Column#74 | N/A | N/A |
| └─TableRowIDScan_26 | 12356.53 | 0 | cop[tikv] | table:t | | keep order:false | N/A | N/A |
±-------------------------------------±---------±--------±----------±-----------------------------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±-------------------------------------------------------------------------------------------------------------±----------±--------+
7 rows in set (0.00 sec)

mysql>

| username: zhanggame1 | Original post link

t.create_time is not in the select return results either, so sorting is meaningless, right?

| username: 小龙虾爱大龙虾 | Original post link

Please post the two execution plan texts.

| username: 春风十里 | Original post link

The second execution plan is better. In fact, for a left join count, the first table is sufficient. The first execution plan actually includes an extra step of scanning the second table, which takes more time. The issue is that using order by (although meaningless) results in a better execution plan. If Optimizer Trace: is well-developed, it might reveal why the optimizer didn’t choose the correct execution plan.

Optimizer Trace: is used to collect and monitor the internal optimization logic process of the optimizer, enhancing the user’s on-site problem diagnosis capability and efficiency, and providing data input for subsequent feedback optimization based on diagnostic monitoring.

I think the solution is to use execution plan binding (SQL Binding).
Execution Plan Management (SPM) | PingCAP Documentation Center

| username: 哈喽沃德 | Original post link

Since you have already counted, why do you still need an order by?

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

Don’t take a screenshot, paste the text or upload it. Also, the one below your screenshot is not explain analyze.

| username: lemonade010 | Original post link

The environments of the two statements are also different.

| username: 帅的掉渣 | Original post link

Sorting is meaningless. Even if create_at has no index, any field involved in sorting will have a similar effect.

| username: 帅的掉渣 | Original post link

Posted, screenshot corrected.

| username: 帅的掉渣 | Original post link

The execution environment is exactly the same. Just executed it again. Updated the screenshot and text.

| username: 帅的掉渣 | Original post link

Execution plan binding can specify indexes and operators. But how can we prohibit the table scan operation on the second table? Can an expert provide more details?

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

It seems that the execution plan of the first SQL is problematic. It should be the same as the second one, ignoring the rrs_person table and directly checking the number of rows in the rrs_attend_partition table that meet the condition project_id = ‘5678a656095428683fef6a90801b4ba’.

| username: zhanggame1 | Original post link

Directly checking the number of rows in the rrs_attend_partition table that meet the condition project_id = '5678a656095428683fef6a90801b4ba' won’t work, as the total number of rows resulting from the join is not solely determined by the table on the left side of the left join.

| username: zhanggame1 | Original post link

How can the count() be accurate if the left join does not scan the right table?

| username: FutureDB | Original post link

Yes, mainly check whether the field connected by the left join is unique. Since the execution plan here does not need to scan the right table, it indicates that the connection field rp.id is a unique key.

| username: wangccsy | Original post link

Got it.

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

Oh, right, then there is a problem with the second execution plan. Does the execution plan not include any content about the rrs_person table? Did the optimizer directly optimize it out?

| username: zhanggame1 | Original post link

I tested it, and if the column on the right table uses a unique constraint for association, then there’s no need to query the right table.

| username: zhanggame1 | Original post link

Tested on version 7.5.0, the draw_id of the game_draw table is the primary key, and the draw_id of table a is a unique constraint. Regardless of whether there is an order by, only the left table is queried.


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

Yes, this has nothing to do with whether or not you use the time field for sorting, right? Why does adding the time field for sorting only make a difference for the left table…