Optimizer Feature Comparison Matrix

The below is a table view. For a more graphical view of the differences see What is the difference between MySQL and MariaDB query optimizers.

FeatureMariaDB 5.3/5.5MariaDB 10.0MySQL 5.5MySQL 5.6
Disk access optimizations
Index Condition Pushdown (ICP)YESYES-YES
Disk-sweep Multi-range read (DS-MRR)YESYES-YES
DS-MRR with Key-ordered retrievalYESYES--
Index_merge / Sort_intersectionYESYES--
Cost-based choice of range vs. index_mergeYESYES--
ORDER BY ... LIMIT <small_limit>-YES-YES
Use extended (hidden) primary keys for innodb/xtradbYES (5.5)YES--
Join optimizations
Batched key access (BKA)YESYES-YES
Block hash joinYESYES--
User-set memory limits on all join buffersYESYES--
Apply early outer table ON conditionsYESYES--
Null-rejecting conditions tested early for NULLsYESYES--
Subquery optimizations
In-to-existsYESYESYESYES
Semi-joinYESYES-YES
MaterializationYESYES-YES
NULL-aware MaterializationYESYES--
Cost choice of materialization vs. in-to-existsYESYES-YES
Subquery cacheYESYES--
Fast explain with subqueriesYESYES--
EXISTS-to-IN-YES--
Optimization for derived tables / views
Delayed materialization of derived tables / materialized viewsYESYES-YES
Instant EXPLAIN for derived tablesYESYES-YES
Derived Table with Keys optimizationYESYES-YES
Fields of merge-able views and derived tables used in equality optimizationsYESYES--
Execution control
LIMIT ROWS EXAMINED rows_limitYES (5.5)YES--
Optimizer control (optimizer switch)
Systematic control of all optimizer strategiesYESYES-partial
EXPLAIN improvements
Explain for DELETE, INSERT, REPLACE, and UPDATE-Partial-YES
EXPLAIN in JSON format---YES
More detailed and consistent EXPLAIN for subqueriesYESYES--
Optimizer trace
Optimizer trace---YES

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.