My Notes on “What’s New in #MySQL 5.6: Optimizer”

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.
  • 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:

  1. Table Pullout: table is pushed up to an INNER JOIN if no duplicate rows result.
  2. First Match: similar idea to an EXISTS condition. Short cut execution when first match is found.
  3. Duplicate Elimination: use temporary table with unique key to eliminate duplicate combinations.
  4. 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.
  5. Inside Out with Materialize Scan creates a temporary and a unique hash index.

Subquery Materialization requires an uncorrelated subquery.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s