| name | mariadb-query-optimization |
| description | Best practices for query optimization in MariaDB — indexing strategies, EXPLAIN analysis, pagination, histogram statistics, and MariaDB-specific optimizer settings. Use when diagnosing slow queries, designing indexes, reviewing schema or query performance, or when queries involve large tables, pagination, GROUP BY, or ORDER BY. Also use when the user asks about MariaDB query performance, EXPLAIN output, or optimizer behavior. |
MariaDB Query Optimization
Last updated: 2026-06-05
Requires: MariaDB 10.1+ for ANALYZE and histograms; optimizer improvements through 11.8 LTS (GA May 2025) form the baseline below.
Default context: Assume MariaDB 11.8 LTS unless the user states another version. Features marked 12.x may be suggested when relevant (including as upgrade options), but always state the minimum version — do not present them as available on 11.8.
What LLMs Get Wrong
| Pattern | What to do instead |
|---|
SELECT * FROM table LIMIT 10 OFFSET 50000 | Use cursor-based pagination — OFFSET scans all skipped rows |
| Blanket rule "functions on indexed columns kill indexes" | Outdated on MariaDB 11.1+/11.3+ for many cases. YEAR(col) = const and UPPER(col) = const on case-insensitive columns can now use indexes — see Functions on indexed columns below |
| Adding an index to a low-cardinality column (boolean, status with 2-3 values) | Optimizer skips indexes with low selectivity and does a table scan anyway |
Not running ANALYZE TABLE after bulk inserts | Histogram statistics become stale; optimizer makes poor plan choices |
Composite index (a, b, c) used in WHERE b = 1 AND c = 2 | Leftmost prefix rule: this skips a, so the index is not used |
SELECT * in queries with JOINs | Name only the columns needed — prevents accidentally blocking covering indexes |
ALTER TABLE t ALTER INDEX idx INVISIBLE to disable an index | That's MySQL syntax. MariaDB uses IGNORED — see Ignored Indexes below |
Jump straight to EXPLAIN or indexes on a slow server | Enable Performance Schema at startup first — it is off by default and cannot be turned on at runtime |
SET GLOBAL performance_schema = ON to enable monitoring | Performance Schema requires performance_schema=ON in my.cnf and a server restart |
Performance Schema (enable first)
Before EXPLAIN, indexes, or query rewrites, confirm the server can observe what queries are doing. MariaDB's Performance Schema is the built-in monitoring layer (10.5+: ~80 tables in the performance_schema database).
Check status:
SHOW VARIABLES LIKE 'performance_schema';
Critical: Performance Schema is disabled by default and cannot be enabled at runtime. If OFF, add to my.cnf and restart:
[mysqld]
performance_schema=ON
After restart, enable the consumers and instruments you need. Scope with WHERE NAME LIKE '...' rather than enabling everything blindly in production — see the overview doc:
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES';
Use performance_schema (waits, stages, statements, and related summary tables) to see where time goes; then use EXPLAIN and ANALYZE below to understand why the optimizer chose a plan. On 10.7.1+, column comments help interpret tables:
SELECT column_name, column_comment
FROM information_schema.columns
WHERE table_schema = 'performance_schema' AND table_name = 'events_statements_summary_by_digest';
Reading EXPLAIN
With Performance Schema available when diagnosing production slowness, run EXPLAIN to inspect the optimizer's plan:
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 10;
Red flags in the output:
| Field | Red flag | What it means |
|---|
type | ALL | Full table scan — missing index or index not used |
key | NULL | No index used despite one existing — check for function on column or type mismatch |
rows | Very high number | Optimizer estimates scanning many rows |
Extra | Using filesort | Expensive sort not covered by an index |
Extra | Using temporary | Temp table created — often from GROUP BY or DISTINCT |
Extra | Using index | ✅ Good — covering index, no table row access needed |
ANALYZE statement (MariaDB 10.1+) actually executes the query and shows real row counts vs. estimates — more reliable than EXPLAIN alone. Note: MariaDB uses ANALYZE, not EXPLAIN ANALYZE:
ANALYZE SELECT * FROM orders WHERE customer_id = 42;
Optimizer Trace shows the optimizer's full decision process. Since MariaDB 12.1 the trace can include full table and view definitions (optimizer_record_context system variable). Since 13.0 it also includes the specific statistics (histograms, index stats) used for cardinality estimates — together they're powerful for diagnosing surprising rows estimates:
SET optimizer_trace = 'enabled=on';
SELECT * FROM orders WHERE customer_id = 42;
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
SET optimizer_trace = 'enabled=off';
Indexing Rules
The Leftmost Prefix Rule
For a composite index (a, b, c), MariaDB can use:
WHERE a = 1 ✅WHERE a = 1 AND b = 2 ✅WHERE a = 1 AND b = 2 AND c = 3 ✅WHERE b = 2 ✗ — skips a, index not usedWHERE a = 1 AND c = 3 — only a part is used
Put the most selective equality conditions first, then range conditions last:
INDEX (status, created_at)
INDEX (created_at, status)
Covering Indexes
A covering index includes all columns needed by the query — no table row access needed (Using index in EXPLAIN):
CREATE INDEX idx_customer_cover ON orders (customer_id, status, created_at);
When NOT to Add an Index
- Low-cardinality columns: a
status column with values active/inactive affects 50% of rows — the optimizer prefers a table scan. Index useful only when combined with other high-selectivity columns.
- Small tables (< a few thousand rows): full scans are faster than index lookups for tiny tables.
- Write-heavy columns: every index slows
INSERT, UPDATE, DELETE — don't index columns that are rarely queried.
Ignored Indexes (not INVISIBLE)
To make the optimizer skip an index without dropping it — useful for testing whether an index is actually needed before removing it — MariaDB uses IGNORED, not MySQL's INVISIBLE:
ALTER TABLE demo ALTER INDEX index_name IGNORED;
ALTER TABLE demo ALTER INDEX index_name NOT IGNORED;
ALTER TABLE demo ALTER INDEX index_name INVISIBLE;
The index is still maintained on writes; it's just hidden from the optimizer. A primary key cannot be ignored. See Ignored Indexes.
Functions on Indexed Columns
The classic rule "any function on an indexed column disables the index" is outdated for MariaDB 11.1+ and 11.4 LTS. The optimizer can now use indexes for a number of common function patterns:
| Pattern | Works on the index? | Since |
|---|
WHERE YEAR(col) = 2025 | ✅ — sargable, picks the right range | 11.1+ (MDEV-8320) |
WHERE DATE(col) <= '2025-12-31' | ✅ — sargable | 11.1+ (MDEV-8320) |
WHERE UPPER(varchar_col) = '...' on a case-insensitive collation (e.g. utf8mb4_uca1400_ai_ci) | ✅ — sargable_casefold=ON is the default | 11.3+ (MDEV-31496) |
WHERE SUBSTR(col, 1, n) = 'abc' | ✅ — leading-prefix SUBSTR is optimized | 11.8+ (MDEV-34911) |
WHERE LOWER(case_sensitive_col) = '...' | ✗ — index not used (collation isn't case-insensitive) | — |
WHERE CAST(col AS UNSIGNED) = 1 or other type-changing transforms | ✗ — index not used | — |
For cases that the optimizer still can't sargabilize, the rewrite-to-range pattern remains valid:
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'
Verify with EXPLAIN rather than assuming: on 11.4+ many "won't use the index" rewrites are now no-ops. If EXPLAIN still shows type=ALL for a sargable pattern, check @@optimizer_switch for sargable_casefold and confirm the column's collation is _ci.
Functional Indexes: Use a Generated Column
MySQL 8.0.13+ supports functional key parts — indexing an expression directly with a doubled-parenthesis syntax. MariaDB does not support this:
CREATE INDEX idx_upper ON users ((UPPER(name)));
ALTER TABLE orders ADD INDEX ((total * quantity));
In MariaDB, index a generated (computed) column instead. A VIRTUAL column stores nothing and is computed on read; the index on it persists the expression's value, which is what gets searched:
ALTER TABLE users
ADD COLUMN name_upper VARCHAR(255) AS (UPPER(name)) VIRTUAL,
ADD INDEX idx_name_upper (name_upper);
SELECT * FROM users WHERE name_upper = 'ALICE';
Note that for many simple cases (YEAR(col), UPPER(col) on _ci collations) MariaDB 11.1+ no longer needs an indexed expression at all — see the table above. Reach for a generated column when the expression isn't sargable on its own.
Pagination: Cursor-Based Instead of OFFSET
OFFSET is a hidden performance trap. LIMIT 10 OFFSET 50000 scans and discards 50,000 rows on every page load.
SELECT id, title FROM posts ORDER BY id DESC LIMIT 10 OFFSET 50000;
SELECT id, title FROM posts ORDER BY id DESC LIMIT 10;
SELECT id, title FROM posts WHERE id < $last_id ORDER BY id DESC LIMIT 10;
For filtered queries, include the filter column in the index alongside id:
CREATE INDEX idx_cat_id ON posts (category, id);
SELECT id, title FROM posts WHERE category = 'news' AND id < $last_id ORDER BY id DESC LIMIT 10;
To detect whether another page exists, fetch LIMIT 11 and check if the 11th row appears.
Histogram Statistics
Histograms let the optimizer understand data distribution on non-indexed columns — critical for query plan quality on complex queries. Without them, the optimizer assumes uniform distribution and can choose wrong join orders.
ANALYZE TABLE orders;
SELECT * FROM mysql.column_stats WHERE table_name = 'orders';
When to run ANALYZE TABLE:
- After bulk inserts or large data changes
- When
EXPLAIN shows unexpectedly high rows estimates
- After initially creating a table and loading data
Tune histogram granularity for tables with highly skewed data distributions:
SET histogram_size = 100;
ANALYZE TABLE orders;
Histograms are collected per-column automatically when using ANALYZE TABLE with histogram_size > 0. They are stored in mysql.column_stats and consulted when optimizer_use_condition_selectivity >= 4 (default in 10.4.1+).
MariaDB Optimizer Switches
MariaDB's optimizer has more tunable flags than MySQL. The most useful for developers:
SELECT @@optimizer_switch\G
SET optimizer_switch = 'derived_merge=off';
SET optimizer_switch = 'derived_merge=on';
Most impactful flags:
| Flag | Default | Effect |
|---|
derived_merge | on | Merges derived tables into outer query — usually faster |
semijoin | on | Optimizes IN/EXISTS subqueries — disable to debug unexpected plans |
subquery_cache | on | Caches correlated subquery results — big win for repeated subqueries |
rowid_filter | on | Pre-filters rowids before fetching rows — helps range queries |
mrr | off | Multi-Range Read — enable for large range scans on spinning disks |
Turn flags off one at a time to isolate which optimization is causing a bad plan, then report via JIRA if a default setting produces a worse plan than the alternative.
Optimizer Improvements in the 10.7–10.11 LTS Window
The 10.11 LTS line bundles features that arrived in the 10.7–10.10 short-term releases:
- JSON-format histograms (10.8+, MDEV-21130, MDEV-26519) — histogram statistics are stored in JSON and are more precise than the older binary format. Just running
ANALYZE TABLE on 10.8+ gives the optimizer better cardinality estimates.
- Descending indexes (10.8+, MDEV-13756) —
CREATE INDEX idx ON t (a ASC, b DESC) is supported; useful for composite ORDER BY a, b DESC patterns and for MIN()/MAX() on descending indexes.
SHOW ANALYZE [FORMAT=JSON] (10.9+, MDEV-27021) — get the optimizer plan and runtime stats for a query running in another connection without intrusion. EXPLAIN FOR CONNECTION syntax also supported (MDEV-10000).- Improved optimization for joins with many
eq_ref tables (10.10+, MDEV-28852, MDEV-26278) — large star-schema-style joins plan dramatically better.
ANALYZE FORMAT=JSON reports time spent in the optimizer itself (10.11+, MDEV-28926) — separates planning time from execution time.
Optimizer Improvements in 11.4 LTS
The 11.4 LTS line continues the overhaul:
- New cost-based cost model (11.0+) — replaces the older rule-based heuristics with a tuned model aware of SSDs and per-engine characteristics.
EXPLAIN and join-order choices in 10.6 vs. 11.4 can differ noticeably on the same query. If you have manual optimizer_adjust_secondary_key_costs settings from 10.x, remove them — they're no-ops on 11.4+.
- Semi-join optimization for single-table
UPDATE/DELETE (11.1+, MDEV-7487) — subqueries inside UPDATE/DELETE can now use the same subquery rewrites that SELECT uses (materialization, semi-join, etc.). Often a large speedup, no rewrite needed.
- Sargable
DATE/YEAR comparisons against constants (11.1+, MDEV-8320) — see Functions on Indexed Columns above.
- Sargable case-folding (11.3+, MDEV-31496,
sargable_casefold on by default) — UCASE/LCASE/UPPER/LOWER on a column with a case-insensitive collation can use the index.
Optimizer Improvements in 11.5–11.8 LTS
These are part of the current LTS baseline — useful for understanding what the optimizer can do today:
- Index Condition Pushdown on partitioned tables (11.5+, MDEV-12404) — previously partitioned tables couldn't use ICP; now they do, often a large speedup on partitioned schemas
ANALYZE shows selectivity of pushed index condition (11.5+, MDEV-18478) — useful when diagnosing whether ICP is helping- Charset Narrowing Optimization on by default (11.8+, MDEV-34380) — eliminates unnecessary character set conversions in WHERE clauses
SUBSTR(col, 1, n) = const_str optimization (11.8+, MDEV-34911) — the optimizer can now use a column index even when the condition is a leading-prefix SUBSTR- Virtual column support in the optimizer (11.8+, MDEV-35616) — see Virtual Column Support in the Optimizer; previously, virtual columns were largely invisible to the optimizer
- Cost-based subquery strategy for single-table
UPDATE/DELETE (11.8+, MDEV-25008) — the optimizer now picks between subquery strategies by cost
Optimizer Improvements in MariaDB 12.x
Several further limitations were lifted in the 12.x rolling releases:
- Rowid filtering on reverse-ordered scans (12.0+) — previously
ORDER BY ... DESC queries couldn't benefit from rowid filtering; now they can
- Index Condition Pushdown on reverse-ordered scans (12.0+) — same fix for ICP
- Loose Index Scan ("Use index for group-by") works with
DESC key parts (12.0+) — previously required ASC indexes
- GROUP BY / ORDER BY can use indexes on virtual columns (12.1+)
- Reorderable LEFT JOIN optimization (12.3+) — the optimizer can now reorder more
LEFT JOIN combinations
- Distinct GROUP BY column inference (12.2+) — derived tables with
GROUP BY are recognized as having distinct group keys, enabling more optimizations downstream
If you target the 11.8 LTS baseline and see a plan that looks needlessly slow on a reverse-ordered or virtual-column query, it may be one of these — verify by running the same query on a 12.x version.
Optimizer Hints
MariaDB 12.0 introduced a comprehensive MySQL-8-style optimizer hints framework (MDEV-35504), with additional hints added through 12.1 and 12.2. Hints go in a /*+ ... */ comment right after SELECT and override the optimizer for one query without changing session settings:
SELECT *
FROM orders o JOIN customers c ON c.id = o.customer_id;
Available hints:
| Hint | Since | Purpose |
|---|
QB_NAME(name) | 12.0 | Name a query block so other hints can target it from outside |
JOIN_FIXED_ORDER / JOIN_ORDER(t1, t2, ...) | 12.0 | Force a join order (JOIN_FIXED_ORDER is similar to STRAIGHT_JOIN) |
JOIN_PREFIX(t1, ...) / JOIN_SUFFIX(t1, ...) | 12.0 | Force specific tables to be first or last in the join order |
MAX_EXECUTION_TIME(ms) | 12.0 | Abort the query if it runs longer than the timeout |
[NO_]MRR / [NO_]BKA / [NO_]BNL | 12.0 | Toggle Multi-Range Read, Batched Key Access, Block Nested Loop |
[NO_]ICP | 12.0 | Toggle Index Condition Pushdown |
[NO_]RANGE_OPTIMIZATION | 12.0 | Toggle range optimizer |
SEMIJOIN(strategy, ...) / SUBQUERY(strategy) | 12.0 | Pick subquery rewrite strategy |
[NO_]INDEX(t idx, ...) / [NO_]JOIN_INDEX / [NO_]GROUP_INDEX / [NO_]ORDER_INDEX | 12.1 | Force / forbid specific index usage by purpose |
[NO_]SPLIT_MATERIALIZED / [NO_]DERIVED_CONDITION_PUSHDOWN / [NO_]MERGE | 12.1 | Control subquery / derived-table optimizations |
[NO_]ROWID_FILTER / [NO_]INDEX_MERGE | 12.2 | Toggle rowid filtering and index merge |
QB_NAME() example — name a subquery so an outer hint can target it:
SELECT *
FROM (
SELECT customer_id, COUNT(*) AS n
FROM orders
GROUP BY customer_id
) t
WHERE n > 10;
Hints are more targeted than SET optimizer_switch because they apply only to the query they're in, not the whole session.
Bounding Expensive Queries: LIMIT ROWS EXAMINED
LIMIT ROWS EXAMINED is a MariaDB-specific extension (since 5.5.21) with no MySQL equivalent. It caps how many rows a SELECT may examine, terminating execution early once the cap is hit — a safety valve against runaway scans on unbounded or ad-hoc queries:
SELECT * FROM t1, t2 LIMIT 10 ROWS EXAMINED 10000;
SELECT * FROM big_table WHERE status = 'x' LIMIT ROWS EXAMINED 50000;
When the cap is reached the query returns a partial result set plus a warning — so it is a guard rail, not a way to get correct-but-faster answers. SELECT only; it is a syntax error on UPDATE/DELETE. For a time-based bound instead, use the MAX_EXECUTION_TIME(ms) optimizer hint (12.0+) above. See LIMIT ROWS EXAMINED.
Quick Wins Checklist
Before adding indexes or rewriting queries, check these first:
- Confirm
performance_schema=ON at startup (restart required if off) — Performance Schema Overview
EXPLAIN the slow query — confirm where the time actually isANALYZE TABLE — stale statistics cause bad plans- Check for functions on indexed columns in
WHERE — note many cases are now sargable on 11.4+ (YEAR(), DATE(), UPPER() on _ci collations)
- Check for
OFFSET in pagination queries
- Verify composite index column order matches query predicates (leftmost prefix)
- Check
EXPLAIN Extra column for Using filesort or Using temporary — these often point to a missing or misordered index
Sources
For topics not covered here, see the official MariaDB documentation at mariadb.com/docs.
