How to Use the ON Condition on Both Left and Right Indexes During a Join

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

Original topic: join 时候如何把on条件用到左右的索引上

| username: 大飞飞呀

[TiDB Usage Environment] Production Environment
[TiDB Version]
[Reproduction Path] What operations were performed to encounter the problem
[Encountered Problem: Problem Phenomenon and Impact]

        maker_side = "sell",
    ) AS char
  ) AS amount,
  count(1) AS cnt,
  "" sub_sql
  table_a a
  JOIN (
      10960001609428992 user_id,
      "2023-09-06" c_date
      10959939290952704 user_id,
      "2023-09-06" c_date
  ) b ON (
    a.taker_user_id = b.user_id
    OR a.maker_user_id = b.user_id
  AND a.created_at >= b.c_date
  AND a.created_at < DATE_ADD(b.c_date, INTERVAL 1 DAY)
  STATUS = 2
  AND taker_user_id <> 0
  AND maker_user_id <> 0
  AND maker_biz_order <> "0"
  AND taker_biz_order <> "0"
	id                                  	task     	estRows   	operator info                                                                                                                                                                                                                                                                                                        	actRows	execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       	memory  	disk
	Projection_16                       	root     	2         	Column#41, Column#42, table_a.product_id, cast(Column#43, var_string(5))->Column#45, Column#44, ->Column#46                                                                                                                                                                                            	24     	time:30.2s, loops:5, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 	110.1 KB	N/A
	└─HashAgg_17                        	root     	2         	group by:Column#69, Column#70, Column#71, funcs:sum(Column#65)->Column#43, funcs:count(1)->Column#44, funcs:firstrow(Column#66)->table_a.product_id, funcs:firstrow(Column#67)->Column#41, funcs:firstrow(Column#68)->Column#42                                                                        	24     	time:30.2s, loops:5, partial_worker:{wall_time:30.241202501s, concurrency:5, task_num:58, tot_wait:2m31.180212434s, tot_exec:25.126475ms, tot_time:2m31.205397686s, max:30.241106062s, p95:30.241106062s}, final_worker:{wall_time:30.24131089s, concurrency:5, task_num:20, tot_wait:2m31.206076662s, tot_exec:111.253µs, tot_time:2m31.206193115s, max:30.24126709s, p95:30.24126709s}                                                                                                                                                                                                                                                                                                                                                            	2.01 MB 	N/A
	  └─Projection_68                   	root     	9545081.25	if(eq(table_a.maker_side, sell), table_a.maker_out_amount, table_a.maker_in_amount)->Column#65, table_a.product_id, Column#41, Column#42, Column#41, Column#42, table_a.product_id                                                             	57366  	time:30.2s, loops:59, Concurrency:5                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  	1.58 MB 	N/A
	    └─Projection_18                 	root     	9545081.25	table_a.product_id, table_a.maker_side, table_a.maker_out_amount, table_a.maker_in_amount, Column#41, Column#42                                                                                                                                              	57366  	time:30.2s, loops:59, Concurrency:5                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  	1.49 MB 	N/A
	      └─HashJoin_34                 	root     	9545081.25	CARTESIAN inner join, other cond:ge(table_a.created_at, cast(Column#42, datetime(6) BINARY)), lt(table_a.created_at, cast(date_add(Column#42, 1, "DAY"), datetime(6) BINARY)), or(eq(table_a.taker_user_id, Column#41), eq(table_a.maker_user_id, Column#41))	57366  	time:30s, loops:59, build_hash_table:{total:52.7µs, fetch:46.9µs, build:5.85µs}, probe:{concurrency:5, total:2m31.1s, max:30.2s, probe:1m46.3s, fetch:44.8s}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      	3.21 KB 	0 Bytes
	        ├─Union_35(Build)           	root     	2         	                                                                                                                                                                                                                                                                                                                     	2      	time:42.6µs, loops:3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                	N/A     	N/A
	        │ ├─Projection_37           	root     	1         	1096000160009428992->Column#41, 2023-09-06->Column#42                                                                                                                                                                                                                                                                	1      	time:3.82µs, loops:2, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               	0 Bytes 	N/A
	        │ │ └─TableDual_38          	root     	1         	rows:1                                                                                                                                                                                                                                                                                                               	1      	time:913ns, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  	N/A     	N/A
	        │ └─Projection_39           	root     	1         	1095993953290952704->Column#41, 2023-09-06->Column#42                                                                                                                                                                                                                                                                	1      	time:5.39µs, loops:2, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               	0 Bytes 	N/A
	        │   └─TableDual_40          	root     	1         	rows:1                                                                                                                                                                                                                                                                                                               	1      	time:1.02µs, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                	N/A     	N/A
	        └─IndexLookUp_67(Probe)     	root     	4772540.62	                                                                                                                                                                                                                                                                                                                     	3960228	time:1.38s, loops:3869, index_task: {total_time: 23.1s, fetch_handle: 407.8ms, build: 331.5µs, wait: 22.7s}, table_task: {total_time: 1m1s, num: 197, concurrency: 5}, next: {wait_index: 1.49ms, wait_table_lookup_build: 401.1µs, wait_table_lookup_resp: 163.9ms}                                                                                                                                                                                                                                                                                                                                                                                                                                                                               	265.0 MB	N/A
	          ├─IndexRangeScan_64(Build)	cop[tikv]	6023305.32	table:a, index:IDX_MAKER_BIZ(maker_biz_order), range:[-inf,"0"), ("0",+inf], keep order:false                                                                                                                                                                                                                        	3960228	time:40ms, loops:3880, cop_task: {num: 118, max: 270.9ms, min: 159.1µs, avg: 47.2ms, p95: 112ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 1s, tot_wait: 9.42ms, rpc_num: 118, rpc_time: 5.57s, copr_cache_hit_ratio: 0.53, build_task_duration: 20.3µs, max_distsql_concurrency: 6}, tikv_task:{proc max:432ms, min:0s, avg: 36.8ms, p80:44ms, p95:200ms, iters:4333, tasks:118}, scan_detail: {total_process_keys: 2154596, total_process_keys_size: 157285508, total_keys: 2154652, get_snapshot_time: 5.62ms, rocksdb: {delete_skipped_count: 1170, key_skipped_count: 2155766, block: {cache_hit_count: 2749, read_count: 2683, read_byte: 27.6 MB, read_time: 7.31ms}}}                                                          	N/A     	N/A
	          └─Selection_66(Probe)     	cop[tikv]	4772540.62	eq(table_a.status, 2), ne(table_a.maker_user_id, 0), ne(table_a.taker_biz_order, "0"), ne(table_a.taker_user_id, 0)                                                                                                                                          	3960228	time:57.2s, loops:4410, cop_task: {num: 6235, max: 545.2ms, min: 0s, avg: 69.1ms, p95: 249.5ms, max_proc_keys: 7140, p95_proc_keys: 2336, tot_proc: 2m2.7s, tot_wait: 3.22s, rpc_num: 5822, rpc_time: 7m11s, copr_cache_hit_ratio: 0.55, build_task_duration: 396.9ms, max_distsql_concurrency: 15, max_extra_concurrency: 5, store_batch_num: 413}, tikv_task:{proc max:548ms, min:0s, avg: 64.7ms, p80:108ms, p95:232ms, iters:25153, tasks:6235}, scan_detail: {total_process_keys: 3088492, total_process_keys_size: 2299212502, total_keys: 3520019, get_snapshot_time: 2.68s, rocksdb: {delete_skipped_count: 6870, key_skipped_count: 1521556, block: {cache_hit_count: 29409387, read_count: 1267864, read_byte: 4.35 GB, read_time: 2.85s}}}	N/A     	N/A
	            └─TableRowIDScan_65     	cop[tikv]	6023305.32	table:a, keep order:false                                                                                                                                                                                                                                                                                            	3960228	tikv_task:{proc max:548ms, min:0s, avg: 64.5ms, p80:108ms, p95:232ms, iters:25153, tasks:6235}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       	N/A     	N/A

This SQL query results in a full table scan on table_a. How can I use an index?
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachment: Screenshot/Log/Monitoring]

| username: 像风一样的男子 | Original post link

What is the significance of table b? Why do I see it as a single table query SQL?

| username: Kongdom | Original post link

Your “on” is an “or,” and “or” probably won’t use an index. I suggest splitting it into two statements and using “union.”

| username: TiDB_C罗 | Original post link

How about writing it like this?

| username: Kongdom | Original post link

:+1: Yes, it feels like this will be faster.

| username: 大飞飞呀 | Original post link

Writing it this way results in a memory overflow.

| username: 大飞飞呀 | Original post link

Do you have something more specific?

| username: 大飞飞呀 | Original post link

Writing it this way is different from the above logic. “Or” represents the same row, even if both conditions match, it will only be recorded once, while “union all” records it twice.