MariaDB Server 13.0 ships MySQL-compatible optimizer hints with view and query block extensions

MariaDB Server 13.0 has released with a full implementation of new-style optimizer hints that allow database administrators and developers to influence query execution plans for individual statements. The syntax mirrors MySQL 8.0's comment-based hint format, enabling control over join ordering, index selection, join buffer usage, derived table merging, subquery strategies, and various optimizations including MRR, ICP, and range optimization.

Beyond MySQL compatibility, MariaDB removes a significant MySQL limitation by allowing nearly all hints inside view definitions rather than restricting views to index hints only. The release also introduces more flexible query block referencing through implicit names based on position or alias, plus explicit block names with path syntax inspired by TiDB, enabling targeted hints within nested views, CTEs, derived tables, and subqueries without requiring QB_NAME annotations in each block.

Optimizer hint capabilities and MariaDB extensions

• MySQL 8.0 compatible syntax: Hints use SELECT /*+ hint(arguments) */ comment-style syntax, matching MySQL 8.0 and TiDB implementations for cross-project portability.
• Comprehensive hint coverage: Users can control join order, index usage, join buffers, derived table merging, condition pushdown, subquery execution strategies, MRR, ICP, range optimization, rowid filtering, lateral derived optimization, and maximum execution time.
• View definition hints: Unlike MySQL, which restricts views to index hints only, MariaDB permits any hint except MAX_EXECUTION_TIME inside CREATE VIEW statements, with hints scoped to objects within that view.
• Flexible query block naming: MariaDB supports explicit query block names, implicit names derived from position or alias, and explicit block names with path syntax for addressing objects nested in views, CTEs, derived tables, and subqueries without requiring QB_NAME hints in each block.
• TiDB-inspired path syntax: The explicit block name with path approach was inspired by TiDB's implementation, addressing the same need to reference objects inside inner query blocks across the MySQL-compatible ecosystem.

Why it matters

For Linux distribution packagers and database administrators running MariaDB in production, these optimizer hints provide practical tools for resolving query performance regressions without rewriting schemas or application code. The MySQL-compatible syntax reduces migration friction for organizations operating mixed database environments, while the view and query block extensions offer capabilities unavailable in upstream MySQL, potentially influencing query tuning workflows across the LAMP stack and cloud-native database deployments.