What does Using join buffer (Block Nested Loop) mean with EXPLAIN mysql command in the Extra column?

Question

I am trying to optimize my query.

And getting Using join buffer (Block Nested Loop) for one of the table with

EXPLAIN SELECT command.

I have no idea what does it mean. I tried googling about, but I haven't found the explanation.

Answer 1

Using join buffer (block nested loop) means the join is unable to use an index, and it's doing the join the hard way. In other words, it's doing a table-scan on the joined table.

The optimizer assumes there could be more rows in the joined table, so it will have to load rows into an in-memory buffer in batches, then compare to these rows in the buffer, to filter those that match the join condition. Then empty the join buffer, fetch the next block of rows, and try again.

If you see Using join buffer (block nested loop) in your EXPLAIN report, you should figure out whether you can add an index to the joined table to allow it to look up matching rows more efficiently. This is a similar process as figuring out the best index for the first table in your query.