--- title: "How to Find and Fix Slow Queries in MySQL and PostgreSQL" description: "Enable the MySQL slow query log, rank queries with pt-query-digest, read EXPLAIN plans and fix them with indexes and rewrites, plus the PostgreSQL equivalents." category: "Database Management" language: en source: https://cubepath.com/docs/database-management/slow-queries-analysis-and-optimization updated: 2026-09-25 --- # How to Find and Fix Slow Queries in MySQL and PostgreSQL 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: ```bash sudo mysql ``` Create a database and an `orders` table with a primary key and no other indexes: ```sql 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: ```sql 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: ```sql SELECT COUNT(*) FROM orders; ``` ```output +----------+ | 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: ```bash sudo nano /etc/mysql/mysql.conf.d/slow-query.cnf ``` ```ini [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: ```bash sudo systemctl restart mysql ``` Verify the settings: ```bash sudo mysql -e "SHOW GLOBAL VARIABLES WHERE Variable_name IN ('slow_query_log', 'slow_query_log_file', 'long_query_time');" ``` ```output +---------------------+-------------------------------+ | Variable_name | Value | +---------------------+-------------------------------+ | long_query_time | 1.000000 | | slow_query_log | ON | | slow_query_log_file | /var/log/mysql/mysql-slow.log | +---------------------+-------------------------------+ ``` > Tip: You can change these variables at runtime with `SET GLOBAL slow_query_log = ON;` without a restart, but the change is lost on the next restart unless it is also in the configuration file. ## 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: ```bash sudo mysql perfdemo ``` ```sql 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: ```bash 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`: ```output # 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: ```bash 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: ```bash 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: ```sql 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: ```sql EXPLAIN SELECT * FROM orders WHERE customer_id = 4242 ORDER BY created_at DESC LIMIT 10; ``` ```output +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+ | 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: | Column | What to look for | |---|---| | `type` | `ALL` is a full table scan. `index` scans a whole index. `range`, `ref`, `eq_ref` and `const` are progressively better. | | `key` | The index actually used. `NULL` means none. | | `rows` | Estimated rows to examine. Compare it with how many rows the query returns. | | `Extra` | `Using 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: ```sql 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: ```sql 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: ```sql EXPLAIN SELECT * FROM orders WHERE customer_id = 4242 ORDER BY created_at DESC LIMIT 10; ``` ```output +----+-------------+--------+------------+------+----------------------+----------------------+---------+-------+------+----------+---------------------+ | 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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`): ```sql 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: ```sql ANALYZE TABLE orders; ``` ```output +-----------------+---------+----------+----------+ | 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: ```bash 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: ```bash sudo nano /etc/postgresql/16/main/postgresql.conf ``` ```ini shared_preload_libraries = 'pg_stat_statements' ``` Restart PostgreSQL and create the extension in the database you want to analyze: ```bash 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: ```sql 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`): ```sql 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.