How to Tune Queries in MariaDB

How do I tune slow queries in MariaDB?

Query tuning in MariaDB is the process of analyzing and rewriting SQL, indexing, and configuring the server to reduce execution time and resource usage.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why is my SELECT slow in MariaDB?

Long execution times usually stem from missing indexes, poor query structure, or insufficient buffer sizes. Begin by capturing the execution plan with EXPLAIN or ANALYZE to see table scans and join methods.

How do I collect a query plan with EXPLAIN?

EXPLAIN shows how the optimizer intends to execute a statement. Use it before a SELECT, UPDATE, or DELETE to reveal access types, key usage, and estimated rows.

Syntax

EXPLAIN [EXTENDED] [FORMAT=JSON] your_query;

Example

EXPLAIN FORMAT=JSON
SELECT c.name, SUM(o.total_amount) AS lifetime_value
FROM Customers c
JOIN Orders o ON o.customer_id = c.id
GROUP BY c.id
ORDER BY lifetime_value DESC
LIMIT 10;

How can I add a missing index?

Look for "type: ALL" or high "rows" counts in EXPLAIN output. Add composite indexes that match the WHERE, JOIN, and ORDER BY columns in left-to-right order.

Syntax

CREATE INDEX index_name
ON table_name (col1, col2 [, col3 ...]);

Example

CREATE INDEX idx_orders_customer_date
ON Orders (customer_id, order_date);

How do I rewrite queries for better performance?

Eliminate SELECT *, move non-sargable functions out of WHERE, and avoid correlated subqueries. Use derived tables or common table expressions (CTEs) for complex aggregations.

What server settings affect query speed?

Tune innodb_buffer_pool_size, join_buffer_size, and query_cache_type (if using MariaDB <10.1). Monitor with SHOW STATUS LIKE 'Handler%'; and adjust iteratively.

What are best practices for MariaDB query tuning?

Use covering indexes, keep transactions short, analyze slow logs, avoid wildcard prefixes in LIKE clauses, and regularly update statistics with ANALYZE TABLE.

Why How to Tune Queries in MariaDB is important

How to Tune Queries in MariaDB Example Usage


-- Identify top 5 products by quantity sold in the last 30 days
EXPLAIN FORMAT=JSON
SELECT p.name, SUM(oi.quantity) AS units
FROM OrderItems oi
JOIN Orders      o ON o.id = oi.order_id
JOIN Products    p ON p.id = oi.product_id
WHERE o.order_date >= CURDATE() - INTERVAL 30 DAY
GROUP BY p.id
ORDER BY units DESC
LIMIT 5;

How to Tune Queries in MariaDB Syntax


EXPLAIN [EXTENDED] [FORMAT=JSON] SELECT ...;
ANALYZE [FORMAT=JSON] SELECT ...;
CREATE [UNIQUE] INDEX index_name ON table_name (col1 [, col2 ...]);
SHOW [SESSION | GLOBAL] STATUS LIKE 'Handler%';

Common Mistakes

Ignoring EXPLAIN output: Many developers add hardware before examining query plans. Always run EXPLAIN to spot full table scans or wrong indexes, then adjust SQL or add indexes accordingly.
Over-indexing tables: Creating indexes on every column slows INSERTs and bloats storage. Add only the minimal composite indexes that match critical WHERE or JOIN patterns.

Frequently Asked Questions (FAQs)

Does ANALYZE run the query?

Yes. Unlike EXPLAIN, ANALYZE FORMAT=JSON SELECT ... executes the statement and returns real runtime statistics, making it ideal for fine-grained tuning on a staging copy.

How can I find slow queries automatically?

Enable slow_query_log and set long_query_time. Review mysqldumpslow output or use Percona Toolkit's pt-query-digest for aggregated insights.

Should I use the MariaDB query cache?

The query cache is deprecated and often causes contention. Prefer proper indexing and application-level caching. If you must use it on older versions, keep it small and monitor hit ratios.