Task #13379

Remove MySQL's optimizer switch and investigate benefits of BNL and BKA optimizer hints

Added by José Raddaoui Marín over 1 year ago. Updated over 1 year ago.

Status:NewStart date:07/03/2020
Priority:MediumDue date:
Assignee:Steve Breker% Done:


Target version:-
Google Code Legacy ID: Tested version:2.6
Sponsored:No Requires documentation:


MySQL 8 introduced changes in the block_nested_loop optimizer switch:

block_nested_loop (default on)

Controls use of BNL join algorithm. In MySQL 8.0.18 and later, this also controls use of hash joins. In MySQL 8.0.20 and later, block nested loop support is removed from the MySQL server, and this flag no longer has any effect; use the BNL or NO_BNL optimizer hint instead, to control whether hash joins are used.


The BNL, NO_BNL and BKA, NO_BKA optimizer hints have to be applied at the statement level, so we should investigate in what queries do they have an impact instead of globally applying the no longer considered block_nested_loop optimizer switch.


#1 Updated by José Raddaoui Marín over 1 year ago

  • Assignee set to Steve Breker

Hi Steve, I believe you analyzed this optimizer at the time and I'd appreciate to hear your thoughts about the new situation when you have some time.

Also available in: Atom PDF