Uncategorized

In MariaDB 13.0, new-style optimizer hints go beyond MySQL’s

spetrunia2Leave a comment

Short

MariaDB 13.0 is now stable. In this release, MariaDB’s support for query optimizer hints reaches maturity and is ready for real-world use.

In order to get here, we’ve extended MySQL’s hint syntax. The most visible change is that the QB_NAME hint now supports “path-based” addressing of query blocks. We’ve discovered we were not the first to add such extension: TiDB also has implemented it. We’ve made MariaDB’s path syntax to be compatible with TiDB.

Long

New-style optimizer hints are put into /*+ */ comment next to the SELECT/UPDATE/DELETE word:

SELECT /*+ NO_INDEX(t1 index1) JOIN_ORDER(t1, t2) */ col1 FROM ...;

Both MySQL and MariaDB share a comprehensive set of hints to control

  • the join order,
  • which indexes should be used,
  • which optimizations should be applied or not, etc.

One can put a collection of hints next to the first SELECT keyword to specify all needed adjustments to the query plan:

SELECT
  /*+ JOIN_ORDER(dt2, dt)
      NO_INDEX(table20@named_select index1)
      JOIN_ORDER(@`select#3` table10, table11)
  */
  select_column1, ...
FROM
  (SELECT /*+ QB_NAME(named_select) */ col1 
   FROM table20 ... WHERE ...) dt
  JOIN
  (SELECT ... FROM table10, table11 WHERE ...) dt2

It is very convenient when doing troubleshooting. You suggest one hint instead of requesting to modify the query in multiple places.

How does one refer to different parts of the query? In MySQL, there are two ways:

  1. Explicit query block names in form of QB_NAME(named_select). Here one has to put another hint at the target select to assign it a name.
  2. Implicit query block names in form of select#n. Those are close to “number of the SELECT word in the query text”, but the actual definition is more complex for queries with CTEs.

Alas, neither kind of names could be used to control SELECTs that come from a VIEW. This is because MySQL does “hint resolution” step before “view processing” step.

Hint path syntax

We didn’t like these limitations. We’ve also learned that TiDB had extended hint syntax to address precisely this
problem. We’ve added a compatible extension. Now, parts of query can be referred by specifying “path” through the derived tables:

SELECT
  /*+ 
    QB_NAME(child_select @derived1 @derived1)    -- denote the innermost select as "child_select" 
    JOIN_ORDER(@child_select t2 t1)              -- specify join order there
  */
FROM
  (select
    from 
      (select ... 
       from t1, t2 where ...
      ) as derived2
   ) AS derived1,
  ...

Path syntax works across VIEWs. Now it’s possible to use hints to control any part of the query. See MariaDB documentation for further details.

Leave a Reply