Optimizer Hints

This section details special comments you can add to SQL statements to influence the query optimizer, helping you manually select better execution plans for improved performance and query tuning.

Optimizer hints are options available that affect the execution plan.

SELECT Modifiers have been in MariaDB for a long time, while Expanded (New-Style) Optimizer Hints were introduced in MariaDB 12.0 and 12.1.

SELECT Modifier Hints

SELECT modifier hints such as HIGH_PRIORITY, SQL_CACHE, SQL_NO_CACHE, and SQL_BUFFER_RESULT that adjust how individual SELECT statements are executed.

Expanded New-Style Optimizer Hints

New-style optimizer hints (MariaDB 12.0+) that give granular, per-query control over optimizer choices without changing server-wide variables.

Index-Level Hints

Index-level optimizer hints that tell the optimizer which indexes to use, ignore, or prefer for a given table.

Query Block Naming

Assign names to query blocks with QB_NAME() so optimizer hints can target specific subqueries or statement blocks.

Table-Level Hints

Table-level optimizer hints that influence join order and per-table optimizer behavior, optionally scoped to a named query block.

See Also

• Use optimizer_switch to enable/disable specific optimizations

_{This page is licensed: CC BY-SA / Gnu FDL}