Optimizer Enhancements in MySQL 5.6: Multi-Range Read (MRR), Block Nested-Loop (BNL), Batched Key Access (BKA)
(Doc ID 1464378.1)
Last updated on NOVEMBER 19, 2024
Applies to:
MySQL Server - Version 5.6 and later Information in this document applies to any platform.
Details
What exactly is optimized
Multi-Range Read (MRR) - MySQL uses temporary buffer for converting set of random row lookups into lookups ordered by row ID (usually primary key).
Block Nested-Loop (BNL) - During (some) join operations (instead of lookups in joined table), MySQL sequentially reads next row from joined table and matches it against portion of joining table located in join_buffer. This approach uses sequential scan instead of random lookups, number of scans depend on size of join_buffer.
Batched Key Access (BKA) - Basically it is the same as MRR, just used during (some) joins to serialize random key lookups into batched partitions (ordered by row ID).
When new approach is faster and troubleshooting
To disable new feature use following commands:
Actions
Contacts
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
