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.
| Feature | MariaDB 5.3/5.5 | MariaDB 10.0 | MySQL 5.5 | MySQL 5.6 |
|---|---|---|---|---|
| Disk access optimizations | ||||
Index Condition Pushdown (ICP) | YES | YES | - | YES |
Disk-sweep Multi-range read (DS-MRR) | YES | YES | - | YES |
DS-MRR with Key-ordered retrieval | YES | YES | - | - |
Index_merge / Sort_intersection | YES | YES | - | - |
Cost-based choice of range vs. index_merge | YES | YES | - | - |
ORDER BY ... LIMIT <small_limit> | - | YES | - | YES |
Use extended (hidden) primary keys for innodb/xtradb | YES (5.5) | YES | - | - |
| Join optimizations | ||||
Batched key access (BKA) | YES | YES | - | YES |
Block hash join | YES | YES | - | - |
User-set memory limits on all join buffers | YES | YES | - | - |
Apply early outer table ON conditions | YES | YES | - | - |
Null-rejecting conditions tested early for NULLs | YES | YES | - | - |
| Subquery optimizations | ||||
In-to-exists | YES | YES | YES | YES |
Semi-join | YES | YES | - | YES |
Materialization | YES | YES | - | YES |
NULL-aware Materialization | YES | YES | - | - |
Cost choice of materialization vs. in-to-exists | YES | YES | - | YES |
Subquery cache | YES | YES | - | - |
Fast explain with subqueries | YES | YES | - | - |
EXISTS-to-IN | - | YES | - | - |
| Optimization for derived tables / views | ||||
Delayed materialization of derived tables / materialized views | YES | YES | - | YES |
Instant EXPLAIN for derived tables | YES | YES | - | YES |
Derived Table with Keys optimization | YES | YES | - | YES |
Fields of merge-able views and derived tables used in equality optimizations | YES | YES | - | - |
| Execution control | ||||
LIMIT ROWS EXAMINED rows_limit | YES (5.5) | YES | - | - |
| Optimizer control (optimizer switch) | ||||
Systematic control of all optimizer strategies | YES | YES | - | partial |
| EXPLAIN improvements | ||||
Explain for DELETE, INSERT, REPLACE, and UPDATE | - | Partial | - | YES |
EXPLAIN in JSON format | - | - | - | YES |
More detailed and consistent EXPLAIN for subqueries | YES | YES | - | - |
| Optimizer trace | ||||
Optimizer trace | - | - | - | YES |
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.
