Giving the Optimizer a Hint: What’s New in MariaDB Server 13.0, compatibility and beyond

The optimizer is one of the most important components of a database server. Most of the time, we trust it to make the right decisions. But sometimes, we know something about our data or workload that the optimizer doesn’t, and we’d like to give it a little help.

With MariaDB Server 13.0, we’re making that easier than ever!

This release introduces a comprehensive set of optimizer hints, using a syntax familiar to MySQL users and supported by other projects in the ecosystem. But as usual, compatibility doesn’t mean we have to stop there. We’ve also added some improvements of our own, giving users even more flexibility when working with complex queries, views, and query blocks.

In this guest post, Oleg Smirnov, Senior Software Engineer at MariaDB plc, walks us through these new capabilities, explains how they compare with MySQL, and highlights a few areas where MariaDB goes a step further.

Let’s dive in!

---

The most recent release of MariaDB Server (version 13.0) ships with a comprehensive set of new-style optimizer hints that let users influence optimizer choices for individual queries.

The hints syntax follows the one MySQL introduced in version 8.0:

SELECT /*+ hint1(arguments) hint2(arguments) */ ... FROM t1
WHERE a IN (SELECT /*+ hint3(arguments) */ ...);

This syntax was also adopted by TiDB, a MySQL-compatible database.

Optimizer hints allow a user to control:

• the order of joining tables (see here);
• use of indexes (see here);
• use of join buffers (see here);
• merging of derived tables (see here);
• pushing conditions into derived tables (see here);
• subquery execution strategies (see here);
• applying optimizations such as MRR, ICP, range optimization, rowid filtering, lateral derived optimization;
• maximum execution time (see here).

Both MariaDB and MySQL implement an almost identical set of hints, with minor differences. However, we noticed a significant limitation in MySQL when it comes to specifying hints inside views. Specifically, MySQL allows only index hints to be placed inside a view definition, such as:

CREATE VIEW v1 AS SELECT /*+ NO_INDEX(t1) */ a FROM t1;

MariaDB does not have this limitation, so any hints except MAX_EXECUTION_TIME can be specified inside the view definition:

CREATE VIEW v1 AS SELECT /*+ NO_MRR(t1 idx_a) JOIN_ORDER(t2, t1)*/ t1.a, t2.a
FROM t1, t2
WHERE t1.a = t2.b;

Naturally, such hints may only refer to objects inside the view.

Another enhancement we added is the ability to refer to a query block without placing a QB_NAME hint inside it. MariaDB therefore supports not only explicit query block names, but also implicit ones based on position or on an alias.

The most flexible option, however, is explicit block names with path, which lets you address objects inside views, derived tables, CTEs and subqueries without adding a QB_NAME hint to them. This was inspired by the TiDB implementation, whose authors presumably faced the same need to refer to objects inside inner query blocks.

The full list of hints with examples of their use can be found on the documentation page. If you encounter a bug or would like to see a new hint implemented, please a ticket in our JIRA – we’ll be happy to help.

---

With these new optimizer hints, MariaDB Server 13.0 gives developers and DBAs more control over query execution, while keeping the syntax familiar to those coming from MySQL.

And it’s not just about compatibility. The ability to use hints inside views and reference query blocks more flexibly shows how we can build on existing standards in the MySQL ecosystem while continuing to innovate.

Of course, optimizer hints are not a replacement for a good optimizer. But when you need that extra bit of control, it’s good to have the right tools available!

A big thank you to Oleg for walking us through these improvements and for the work behind them.

As always, we encourage you to try MariaDB Server 13.0, experiment with the new hints, and share your feedback. Found a bug? Missing a hint that would make your life easier? Let us know through MariaDB’s Jira.

MariaDB Server is built in the open, and your feedback helps us make it better for everyone.