NLJ

Aikesheng Open Source Community

Jan 19, 2020 · Databases

MySQL Join Optimization: Understanding BNL vs NLJ and Index Issues with Character Set Mismatches

This article analyzes a slow MySQL LEFT JOIN query, explains why the optimizer chose the inefficient Block Nested Loop algorithm instead of Index Nested Loop, shows how character‑set and collation differences cause index loss, and demonstrates how converting to INNER JOIN or fixing indexes restores high performance.