Have just watched the “What’s New in MySQL 5.6: Optimizer” webinar. My notes are below.
The main features are:
- Filesort optimization with small LIMIT done in memory instead of to disk.
- Index Condition Pushdown (ICP) down to storage engine.
- Multi-Range Read (MRR) combines multiple index conditions into a single scan. Retrieves matching rowids from index, then sorts list of rowids for a sweep-read of the table.
- Join using Batched Key Access (BKA) uses MRR and a Join buffer to improve performance for nested loop (NL) joins.
- EXPLAIN for INSERT/UPDATE/DELETE.
- Optimizer Traces (SET SESSION.OPTIMIZER_TRACE=’enabled=on’;) in JSON format.
- Structured EXPLAIN in JSON format—much more informative. (EXPLAIN FORMAT=JSON).
- Subquery Execution
Subquery execution has been a weakness in MySQL. Improved performance is achieved by semi-join strategies and materialization.
Semi-join strategies are:
- Table Pullout: table is pushed up to an INNER JOIN if no duplicate rows result.
- First Match: similar idea to an EXISTS condition. Short cut execution when first match is found.
- Duplicate Elimination: use temporary table with unique key to eliminate duplicate combinations.
- Inside Out with Loose Scan. Joining inner tables before outer tables. Loose scan is index scan. Uncorrelated subquery and have an index covering the subquery’s select list.
- Inside Out with Materialize Scan creates a temporary and a unique hash index.
Subquery Materialization requires an uncorrelated subquery.