Sample Header Ad - 728x90

working of index in join query

1 vote
1 answer
37 views
My question is regarding the
output. Please refer the below query and its
command output
Explain 
update 
test.table1 t1 
join test.table2 t2 
on t1.field1=t2.field1
and t1.field2=t2.field2
set  t2.field3=t1.field3;
Below is the result of the EXPLAIN command. enter image description here I added a composite index on (
,
) to both
and
, and then executed the query. After adding the index, the query performance improved significantly. Based on the output of the
command, I confirmed that the query is properly utilizing the index. However, I have a question regarding the details of the
output. Specifically, there is no indication of index usage for
. In the
output (please refer to the image), the
field for
is shown as NULL. Does this mean that the index on
is not being used? If so, how is the query executing so quickly? In other words, if no index is used on
, what mechanism is allowing the matching process in
to perform so efficiently? I am using MySQL 8.0.42
Asked by jithin giri (209 rep)
Jun 3, 2025, 12:16 PM
Last activity: Jun 3, 2025, 03:23 PM