Task #13379

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

Added by José Raddaoui Marín about 1 month ago. Updated about 1 month ago.

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

0%

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

Description

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.

https://dev.mysql.com/doc/refman/8.0/en/switchable-optimizations.html
https://dev.mysql.com/doc/refman/8.0/en/bnl-bka-optimization.html
https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-table-level

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.

History

#1 Updated by José Raddaoui Marín about 1 month 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