Most database performance problems come from a handful of queries that read far more rows than they return. The reliable way to fix them is a loop: log the slow queries, rank them by total cost, read the execution plan of the worst one, change an index or the query, and measure again. In this tutorial you will run that loop on MySQL 8.0 on Ubuntu 24.04 with a sample table, and then see the equivalent tools for PostgreSQL.

Prerequisites

To follow this tutorial you need:

  • A server running Ubuntu 24.04 LTS, for example a CubePath VPS.
  • A non-root user with sudo privileges.
  • MySQL 8.0 installed with sudo apt install mysql-server. On Ubuntu the MySQL root account uses socket authentication, so sudo mysql opens a root session without a password.
  • For the PostgreSQL section, PostgreSQL 16 installed with sudo apt install postgresql.

Run the examples on a test server or a copy of your data first. Adding an index to a large production table uses I/O and, on older versions or some column types, can block writes.

Step 1 - Creating a sample table

A realistic example makes the plans easier to follow. Open a MySQL session:

sudo mysql

Create a database and an orders table with a primary key and no other indexes:

CREATE DATABASE perfdemo;
USE perfdemo;

CREATE TABLE orders (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    status      VARCHAR(20) NOT NULL,
    total       DECIMAL(10,2) NOT NULL,
    created_at  DATETIME NOT NULL
);

Fill it with 500,000 rows using a recursive common table expression. MySQL limits recursion to 1,000 levels by default, so raise the limit for this session first:

SET SESSION cte_max_recursion_depth = 1000000;

INSERT INTO orders (customer_id, status, total, created_at)
WITH RECURSIVE seq (n) AS (
    SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 500000
)
SELECT FLOOR(1 + RAND() * 50000),
       ELT(1 + FLOOR(RAND() * 3), 'pending', 'paid', 'shipped'),
       ROUND(RAND() * 500, 2),
       NOW() - INTERVAL FLOOR(RAND() * 365) DAY
FROM seq;

Confirm the row count:

SELECT COUNT(*) FROM orders;

+----------+
| COUNT(*) |
+----------+
|   500000 |
+----------+

Leave the session with exit.

Step 2 - Enabling the slow query log

The slow query log records every statement that takes longer than long_query_time seconds, with how many rows it examined and returned. Create a separate configuration file so package upgrades do not overwrite your settings:

sudo nano /etc/mysql/mysql.conf.d/slow-query.cnf

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = ON
log_throttle_queries_not_using_indexes = 60

What each option does:

  • long_query_time = 1 logs statements that run for more than one second. Lower it to 0.5 or 0.2 once the worst offenders are fixed.
  • log_queries_not_using_indexes also logs fast queries that scan a whole table. These are often fine today and slow tomorrow when the table grows.
  • log_throttle_queries_not_using_indexes = 60 writes at most 60 of those per minute, so the log cannot flood the disk.

Restart MySQL to load the file:

sudo systemctl restart mysql

Verify the settings:

sudo mysql -e "SHOW GLOBAL VARIABLES WHERE Variable_name IN ('slow_query_log', 'slow_query_log_file', 'long_query_time');"

+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| long_query_time     | 1.000000                      |
| slow_query_log      | ON                            |
| slow_query_log_file | /var/log/mysql/mysql-slow.log |
+---------------------+-------------------------------+

Step 3 - Capturing and ranking slow queries

On the sample table most queries finish in well under a second, so for this demonstration lower the threshold for your session only. long_query_time can be set per session, which is also useful to profile one specific job in production:

sudo mysql perfdemo

SET SESSION long_query_time = 0;

SELECT * FROM orders WHERE customer_id = 4242 ORDER BY created_at DESC LIMIT 10;
SELECT COUNT(*) FROM orders WHERE YEAR(created_at) = 2026;
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 400000;

In another terminal, look at the end of the log:

sudo tail -n 6 /var/log/mysql/mysql-slow.log

Each entry shows the time spent and, most importantly, Rows_examined compared with Rows_sent:

# Time: 2026-09-25T10:15:32.123456Z
# User@Host: root[root] @ localhost []  Id:    12
# Query_time: 0.214530  Lock_time: 0.000004 Rows_sent: 10  Rows_examined: 500010
SET timestamp=1790331332;
SELECT * FROM orders WHERE customer_id = 4242 ORDER BY created_at DESC LIMIT 10;

Reading 500,010 rows to return 10 is the signature of a missing index.

On a real server the log quickly holds thousands of entries. Group them by query shape with pt-query-digest from the Percona Toolkit, which is packaged in Ubuntu:

sudo apt install percona-toolkit
sudo pt-query-digest /var/log/mysql/mysql-slow.log > ~/slow-report.txt

Open ~/slow-report.txt. The Profile section at the top ranks queries by total response time, which is the right order to work in: a 50 ms query that runs 100,000 times a day costs more than a 5 second report that runs once. Below it, each query has its own block with the number of calls, the time distribution, rows examined and a sample statement.

If you cannot install extra packages, mysqldumpslow ships with MySQL and gives a simpler summary sorted by total time:

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

Using the Performance Schema instead of the log

MySQL 8.0 also aggregates statistics for every normalized statement in the Performance Schema, whether or not it crossed long_query_time. The sys schema presents them in readable form:

SELECT query, exec_count, total_latency, avg_latency, rows_examined_avg, rows_sent_avg
FROM sys.statement_analysis
WHERE db = 'perfdemo'
ORDER BY total_latency DESC
LIMIT 5;

A large gap between rows_examined_avg and rows_sent_avg points at the same problem as the slow log. sys.statements_with_full_table_scans lists only statements that did not use an index.

Step 4 - Reading the execution plan with EXPLAIN

EXPLAIN shows how MySQL plans to run a query without executing it. Run it for the first query:

EXPLAIN SELECT * FROM orders WHERE customer_id = 4242 ORDER BY created_at DESC LIMIT 10;

+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 498700 |    10.00 | Using where; Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+

The columns that matter most:

ColumnWhat to look for
typeALL is a full table scan. index scans a whole index. range, ref, eq_ref and const are progressively better.
keyThe index actually used. NULL means none.
rowsEstimated rows to examine. Compare it with how many rows the query returns.
ExtraUsing filesort means an extra sort step. Using temporary means an internal temporary table. Using index means the index alone answered the query.

EXPLAIN ANALYZE runs the query and reports real timings and row counts for each step of the plan. Use it when estimates and reality might differ:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 4242 ORDER BY created_at DESC LIMIT 10\G

The output is a tree read from the innermost line outward. For this query you will see a Table scan on orders reading all 500,000 rows, a Filter that keeps about ten of them and a Sort on top. Because EXPLAIN ANALYZE executes the statement, never run it on an UPDATE or DELETE you do not want applied.

Step 5 - Adding the right index

The query filters by customer_id and sorts by created_at. A composite index on both columns, in that order, lets MySQL jump straight to the customer's rows and read them already sorted:

CREATE INDEX idx_customer_created ON orders (customer_id, created_at);

Column order matters: put columns compared with = first, then the column used for a range or ORDER BY. An index on (created_at, customer_id) would not help this query much.

Check the plan again:

EXPLAIN SELECT * FROM orders WHERE customer_id = 4242 ORDER BY created_at DESC LIMIT 10;

+----+-------------+--------+------------+------+----------------------+----------------------+---------+-------+------+----------+---------------------+
| id | select_type | table  | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra               |
+----+-------------+--------+------------+------+----------------------+----------------------+---------+-------+------+----------+---------------------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_customer_created | idx_customer_created | 4       | const |   10 |   100.00 | Backward index scan |
+----+-------------+--------+------------+------+----------------------+----------------------+---------+-------+------+----------+---------------------+

The scan is gone (type is ref), MySQL estimates 10 rows instead of about 500,000, and the filesort disappeared because the index returns rows in order. Run the query again and the time drops from hundreds of milliseconds to around a millisecond.

Before adding indexes everywhere, remember the trade-off: every index slows down INSERT, UPDATE and DELETE on that table and uses disk and memory. Index for the queries that the ranking in Step 3 says are expensive, and remove indexes nobody uses:

SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'perfdemo';
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'perfdemo';

schema_unused_indexes is based on statistics collected since the last restart, so only trust it on a server that has been running a normal workload for a while.

Step 6 - Rewriting queries that cannot use an index

Some queries stay slow even with an index because of the way they are written.

Functions on indexed columns

The second query wraps the column in a function:

SELECT COUNT(*) FROM orders WHERE YEAR(created_at) = 2026;

MySQL cannot use an index on created_at for this, because it would have to compute YEAR() for every row. Add an index on the column and rewrite the condition as a range:

CREATE INDEX idx_created ON orders (created_at);

SELECT COUNT(*) FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

EXPLAIN now shows type: range with Using index. The same rule applies to DATE(col) = ..., LOWER(col) = ... and arithmetic such as col + 1 = 10. If you cannot change the query, MySQL 8.0 supports functional indexes, for example CREATE INDEX idx_email_lower ON users ((LOWER(email)));.

Leading wildcards

WHERE email LIKE '%@example.com' cannot use a B-tree index because the value does not have a known prefix. LIKE 'john%' can. For suffix or free-text search, store a separate column (for example the email domain) and index it, or use a FULLTEXT index.

Deep OFFSET pagination

The third query asks for page 40,001 of 10 rows:

SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 400000;

MySQL has to read and discard 400,000 rows to find the ten you want, and it gets slower with each page. Use keyset pagination instead: remember the last id of the previous page and continue from it:

SELECT * FROM orders WHERE id > 400000 ORDER BY id LIMIT 10;

This reads exactly ten rows through the primary key, no matter how deep the page is.

Selecting only what you need

SELECT * forces MySQL to read every column from the table row, even when an index already contains the columns you need. When a query only needs indexed columns, the index can answer it alone (a covering index, shown as Using index in Extra):

EXPLAIN SELECT customer_id, created_at FROM orders WHERE customer_id = 4242;

Step 7 - Keeping statistics up to date

The optimizer chooses plans from table statistics. InnoDB refreshes them automatically when about 10% of a table changes, but after bulk loads or large deletes the estimates in EXPLAIN can be far off. Refresh them manually:

ANALYZE TABLE orders;

+-----------------+---------+----------+----------+
| Table           | Op      | Msg_type | Msg_text |
+-----------------+---------+----------+----------+
| perfdemo.orders | analyze | status   | OK       |
+-----------------+---------+----------+----------+

If a plan is still wrong after ANALYZE TABLE, compare EXPLAIN estimates with EXPLAIN ANALYZE actual rows to find the step where they diverge.

Finding slow queries in PostgreSQL

The workflow is the same in PostgreSQL; only the tools change.

Logging slow statements

Log every statement that takes longer than 500 ms. ALTER SYSTEM writes the setting to postgresql.auto.conf, and a reload applies it:

sudo -u postgres psql -c "ALTER SYSTEM SET log_min_duration_statement = '500ms';"
sudo -u postgres psql -c "SELECT pg_reload_conf();"

Slow statements appear in /var/log/postgresql/postgresql-16-main.log with a line such as LOG: duration: 812.345 ms statement: SELECT ....

Ranking queries with pg_stat_statements

pg_stat_statements is PostgreSQL's equivalent of the Performance Schema digest tables. It is included in Ubuntu's PostgreSQL packages but must be loaded at startup. Edit the main configuration file:

sudo nano /etc/postgresql/16/main/postgresql.conf

shared_preload_libraries = 'pg_stat_statements'

Restart PostgreSQL and create the extension in the database you want to analyze:

sudo systemctl restart postgresql
sudo -u postgres psql -d your_database -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

List the statements with the highest total execution time:

SELECT calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 1)  AS mean_ms,
       rows,
       left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Reset the counters after a fix to measure the new baseline with SELECT pg_stat_statements_reset();.

Reading PostgreSQL plans

Use EXPLAIN (ANALYZE, BUFFERS) to run the query and see real timings and how many pages came from cache (shared hit) or disk (read):

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 4242 ORDER BY created_at DESC LIMIT 10;

If you run it against a table like the orders example, look for Seq Scan on large tables, a large difference between rows= estimated and actual ... rows=, and Sort Method: external merge Disk, which means the sort did not fit in work_mem. The index fix is the same as in MySQL: CREATE INDEX CONCURRENTLY idx_customer_created ON orders (customer_id, created_at);. CONCURRENTLY builds the index without blocking writes. Refresh statistics with ANALYZE orders;.

Troubleshooting

  • The slow log file stays empty. Check that slow_query_log is ON and that the file path is writable by the mysql user. On Ubuntu, AppArmor only lets MySQL write logs under /var/log/mysql/, so keep the file there.
  • MySQL ignores a new index. The optimizer may estimate that a scan is cheaper, which is correct when the condition matches a large share of the table (for example status = 'paid' on a third of the rows). Run ANALYZE TABLE and compare with EXPLAIN ANALYZE. Low-selectivity columns rarely benefit from an index on their own.
  • Queries are slow but examine few rows. The time is spent waiting, not reading. Check Lock_time in the slow log and look for blocking transactions with SELECT * FROM sys.innodb_lock_waits;.
  • High CPU with many short queries. Rank by exec_count in sys.statement_analysis instead of latency. An N+1 pattern in the application (one query per row of a list) is a common cause, and the fix belongs in the application code.

Conclusion

You enabled the slow query log, ranked queries by total cost, read execution plans and fixed the three most common causes of slow queries: missing composite indexes, functions on indexed columns and deep OFFSET pagination. Repeat the loop after each release, because new code brings new query shapes. As next steps, set up log rotation for /var/log/mysql/mysql-slow.log if it grows quickly, review innodb_buffer_pool_size so your working set fits in memory, and track query latency over time in your monitoring system.