1

My question is regarding the Explain output. Please refer the below query and its Explain 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 (field1, field2) to both table1 and table2, and then executed the query. After adding the index, the query performance improved significantly. Based on the output of the EXPLAIN command, I confirmed that the query is properly utilizing the index.

However, I have a question regarding the details of the EXPLAIN output. Specifically, there is no indication of index usage for table1. In the EXPLAIN output (please refer to the image), the key field for table1 is shown as NULL.

Does this mean that the index on table1 is not being used?

If so, how is the query executing so quickly? In other words, if no index is used on table1, what mechanism is allowing the matching process in table1 to perform so efficiently?

I am using MySQL 8.0.42

Improve this question
4
  • All records in table1 are checked, to see if they match a record in table2, because of all, no index is needed/used. Commented Jun 3 at 12:55
  • When you add order by t1.field1, t1.fiel2, the index will be used. Commented Jun 3 at 12:56
  • But, even if such an index is added on t1, the query won't go much faster. Commented Jun 3 at 21:59
  • Adding an index, may make the query go faster. the catch is in may, because there is no need for the query to go faster because of the index being added.... Commented Jun 7 at 9:46

1 Answer 1

Reset to default
2

Suppose you are reading a book. You read it on page at a time, from front to back. It takes a while.

Now suppose that for each page you turn, I ask you to read through a second book, from front to back. You will have to read the second book fully for every page of the first book. That's going to take a lot longer!

That's what a join is like when you don't have indexes. For every row of table1, you have to read every row of table2. The time is proportional to the number of rows in each table multiplied together.

But what if you didn't have to read every page of the second book? What if you could just read one page each time?

That's what an indexed join is like. You still have to read every row of the first table, but then to lookup the matching row in the second table, it's much less costly. The time is more proportional to the number of rows in the first table.

Improve this answer
1
  • Thank you for the clear and helpful explanation. Much appreciated! Commented Jun 5 at 6:05

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.