Skip to content
Tutorials Center
My learning

Topics

Lesson 33 of 40 ยท Indexes and Performance

Query Tuning and the Slow Query Log in MariaDB

Find slow MariaDB queries with the slow query log and mariadb-dumpslow, then fix them: keyset pagination, FORCE INDEX, statistics and histograms, batching, buffer pool and timeouts.

Performance work should start from evidence. Which statements take the most total time? Which ones run a million times a day? Which scan far more rows than they return? MariaDB gives you the slow query log, performance_schema, status counters and EXPLAIN to answer those questions, and a short list of techniques fixes most of what you find.

This lesson covers collecting evidence, the techniques that pay off most often, and a few server settings. Server configuration is read-only here; the query patterns have runnable portable versions.

Step 1: log slow queries

The slow query log records statements that run longer than a threshold.

SQL
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;               -- seconds
SET GLOBAL log_slow_verbosity = 'query_plan,explain';   -- extra detail (MariaDB)
SET GLOBAL log_queries_not_using_indexes = ON;  -- noisy: use for short spells
SET GLOBAL slow_query_log_file = '/var/log/mysql/mariadb-slow.log';

Make the settings permanent in the [mysqld] section of the option file. A log entry shows the query time, lock time, rows sent and rows examined; a query that examines 500,000 rows to return 10 is the classic index problem. MariaDB adds Rows_affected, Full_scan, Tmp_table and similar flags to the entries.

Summarize the log with the bundled tool, which groups similar statements by pattern:

Terminal
mariadb-dumpslow -s t -t 10 /var/log/mysql/mariadb-slow.log     # top 10 by total time
mariadb-dumpslow -s c -t 10 /var/log/mysql/mariadb-slow.log     # by count

(mysqldumpslow is the older name.) Third-party tools such as pt-query-digest give richer reports. Sort by total time, not by the single slowest query: a 20 ms query run 5 million times matters more than one 8 s monthly report.

Live views: SHOW FULL PROCESSLIST; (what is running now), SHOW STATUS LIKE 'Handler_read%'; (Handler_read_rnd_next high = table scans), and performance_schema.events_statements_summary_by_digest (enable performance_schema=ON).

Step 2: the techniques that pay off

2.1 Select only what you need

SELECT * forces row reads, ships unneeded columns, and prevents covering indexes. Name the columns, and add LIMIT when the application shows only a page.

2.2 Paginate with a key, not OFFSET

LIMIT 20 OFFSET 100000 makes the server read and discard 100,000 rows. Keyset (seek) pagination remembers the last key and continues from it:

SQL
-- slow on deep pages
SELECT id, title FROM books ORDER BY id LIMIT 20 OFFSET 100000;
-- fast: starts at the index position
SELECT id, title FROM books WHERE id > 100020 ORDER BY id LIMIT 20;

For non-unique sort keys, include a tie-breaker: WHERE (published, id) > (2020, 55) ORDER BY published, id.

SQL OFFSET versus keyset pagination ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Both return the same rows here, but the keyset form seeks straight to id > 6 through the primary key.

2.3 Replace per-row work with set-based SQL

An application loop that runs one query per row (the "N+1 problem") turns a 10 ms page into hundreds of round trips. Join once, or fetch with IN (...).

SQL One join instead of N queries ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL
SELECT a.name FROM authors a
WHERE EXISTS (SELECT 1 FROM books b WHERE b.author_id = a.id AND b.price > 20);

EXISTS stops at the first match. MariaDB's optimizer (10.0+) transforms many IN (subquery) forms into semi-joins automatically, so both are usually fine today, but check EXPLAIN when in doubt.

2.5 Batch writes

One INSERT with many rows is far faster than many single-row inserts, because it pays the parse, round trip and commit cost once. Wrap bulk work in one transaction and load big files with LOAD DATA INFILE:

SQL
INSERT INTO log_entries (level, message) VALUES ('info','a'),('warn','b'),('info','c');   -- 1 statement
LOAD DATA LOCAL INFILE '/tmp/rows.csv' INTO TABLE log_entries
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;
SQL Multi-row insert in one transaction

Press Run (or Ctrl + Enter) to see the result here.

2.6 Avoid work you do not need

  • COUNT(*) on a huge InnoDB table scans an index (InnoDB keeps no row counter). Cache the number, estimate it from statistics, or show "more than N".
  • ORDER BY RAND() sorts the whole table; pick a random id range instead.
  • DISTINCT and UNION (without ALL) add sort or temp-table work; use UNION ALL when duplicates are impossible.
  • A leading wildcard LIKE '%x' cannot use a B-tree index: use FULLTEXT.

Step 3: help the optimizer

  • Statistics: ANALYZE TABLE t; refreshes InnoDB statistics. MariaDB also has engine-independent statistics with histograms: SET SESSION histogram_size = 254; ANALYZE TABLE t PERSISTENT FOR ALL; set use_stat_tables='preferably' so skewed columns get better row estimates.
  • Index hints: SELECT ... FROM t USE INDEX (idx_a), FORCE INDEX (idx_a) or IGNORE INDEX (idx_b) override a wrong choice. Use sparingly: they hard-code a decision that data growth can make wrong. STRAIGHT_JOIN forces join order.
  • Optimizer switches: SHOW VARIABLES LIKE 'optimizer_switch'; lists strategy flags (index_merge, semijoin, derived_merge...). Change per session to experiment, not globally.
  • Optimizer trace: SET optimizer_trace='enabled=on'; SELECT ...; SELECT * FROM information_schema.OPTIMIZER_TRACE\G explains why a plan was chosen.

Step 4: protect the server

SQL
SET SESSION max_statement_time = 5;        -- seconds; kills this session's SELECTs that run longer
-- or limit just one statement:
SET STATEMENT max_statement_time=2 FOR SELECT COUNT(*) FROM big_table;

max_statement_time (MariaDB 10.1+) is a guard rail against runaway reporting queries. Also set wait_timeout, limit max_connections, and give report workloads a replica (replication).

Key InnoDB settings to know: innodb_buffer_pool_size (on a dedicated database host often 60 to 75% of RAM; the most important setting), innodb_log_file_size (bigger = fewer checkpoint stalls on write-heavy loads), innodb_flush_log_at_trx_commit (1 = safest, 2 = faster with up to a second of loss on OS crash), tmp_table_size/max_heap_table_size (in-memory temp tables). Measure before and after each change; avoid folklore settings.

Common mistakes

  • Optimizing without measuring, or only the single slowest query.
  • Adding indexes for every slow query without checking write cost.
  • Deep OFFSET pagination.
  • Running SELECT * over wide rows (TEXT/BLOB) when 3 columns are needed.
  • Using FORCE INDEX everywhere instead of fixing statistics.
  • Tuning on a laptop dataset: tune against realistic volumes.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does the slow query log record?

    Enable it with slow_query_log=ON and set long_query_time to the threshold you care about.
  2. 2. Why is keyset pagination faster than LIMIT/OFFSET for deep pages?

    WHERE id > last_seen seeks directly, while OFFSET reads and throws away rows.
  3. 3. Which command refreshes the statistics the optimizer relies on?

    ANALYZE TABLE updates index statistics; with PERSISTENT FOR it builds engine-independent statistics and histograms.
  4. 4. What is max_statement_time for?

    It protects the server from runaway queries; SET STATEMENT can set it for a single query.

Summary

  • Measure first: slow query log, mariadb-dumpslow, processlist, performance_schema.
  • Biggest wins: right indexes, selecting fewer columns, keyset pagination, set-based SQL and batched writes.
  • Keep statistics fresh; use hints and optimizer switches as a last resort.
  • Guard rails (max_statement_time, replicas) and a properly sized buffer pool keep the server healthy.

Next: Replication and Galera Cluster