Skip to content

Lesson 33 ยท 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