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.

Polymerize

dev.mysql.com/doc/refman/8.0/en/… – Nonproductive

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.

Widower

But what does it mean when this happens despite the fact that there IS an index in the join table for the very column used in the ON condition? – Trichite

Depends what the join condition is. I suggest you ask a new question, and make sure you show the query you're trying to optimize, and the result of EXPLAIN for that query, and SHOW CREATE TABLE for each of the table(s) in the query. – Widower

Ok, turns out it's a known mysql bug causing a bogus block nested loop: bugs.mysql.com/bug.php?id=69721 – Trichite

Answer 2

I ran into the same issue in MySQL 5.6 as well.

After a lot of trial and error, I ran OPTIMIZE TABLE query on my table and that fixed the issue for me.

Indescribable

I would advise that one not do this in production if you use InnoDB. One reason is that this does very little to improve InnoDB table performance, and the second reason is that this is likely to lock your table even if your table isn't that big; for example a 500mb table with ~5million rows. - dev.mysql.com/doc/refman/8.0/en/optimize-table.html – Lamppost

Despite the good warning from @Lamppost above, I did the optimize (because the table was small, and because the production db was showing the error but the dev db wasn't, and because I was desperate) and the optimize reported "Table does not support optimize, doing recreate + analyze instead" followed by "OK". After that, the nested loop error was gone and my query didn't hang anymore. Nice. – Bigley

Answer 3

I also ran into this issue with MySQL 5.6. This comes up when a join is being attempted between 2 tables(either via a join or a subquery).

This occurs because the optimizer isn't able to optimize the query. Some causes for this include:

1. There's no suitable index to be used for the join
2. The join condition is a string instead of an SQL clause

For the first scenario, check that the column on the main table and the column on the joined table both have indexes. For example:

Select * from orders o inner join users u on o.user_id = u.id

Make sure that the user_id column on the orders table and the id column on the users table are both indexed.

For the second scenario, if you specify the join condition as a string(something that MySql supports), then the optimizer won't be able to optimize the query. For example:

Select * from orders o inner join users u on 'o.user_id = u.id'

When you run an explain on this, you'll see 'Using join buffer (Block Nested Loop)' come up in the output. Instead, the query should be:

Select * from orders o inner join users u on o.user_id = u.id

In my own scenario, the issue was the second one; my join condition was using a string.

Lamppost