Diagnosing slow database performance: slow query optimization with EXPLAIN

Slow query optimization starts with the execution plan, not with adding indexes at random. EXPLAIN shows how the database intends to run a statement — which tables it scans, which indexes it uses and how many rows it expects to touch — so you can fix the actual cause. This guide walks through reading plans in MySQL 8, MariaDB and PostgreSQL, then adding or rewriting the parts that cost the most.

Before you start

You need:

• SSH access to your Linux VPS and a database user that can run SELECT and CREATE INDEX on the schema you are tuning.
• The mysql or psql client installed, or the ability to connect from your workstation.
• A slow statement to work on. If you do not have one yet, capture it first with the MySQL slow query log.
• Ideally a staging copy or a read replica. Index changes on a large production table can take time and hold locks.

Connect to the server and open a session:

ssh deploy@203.0.113.10
mysql -u deploy -p -h 127.0.0.1 shop

psql -U deploy -h 203.0.113.10 -d shop

Read the plan with EXPLAIN

Prefix the query with EXPLAIN. The database returns the plan instead of the rows.

EXPLAIN SELECT o.id, o.total, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 20;

MySQL prints one row per table access:

id  select_type  table  type    key      rows    Extra
1   SIMPLE       o      ALL     NULL     480213  Using where; Using filesort
1   SIMPLE       c      eq_ref  PRIMARY  1       NULL

Read it left to right, top to bottom. The first row is usually the table the optimiser starts from. Two things stand out here: type: ALL on orders means a full table scan, and Using filesort means the sort could not be satisfied by an index.

PostgreSQL uses a tree layout instead:

EXPLAIN SELECT o.id, o.total, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 20;

Limit  (cost=0.00..18422.10 rows=20 width=48)
  ->  Sort  (cost=18422.10..18422.15 rows=20 width=48)
        Sort Key: o.created_at DESC
        ->  Seq Scan on orders o  (cost=0.00..17890.00 rows=212 width=48)
              Filter: ((status)::text = 'pending'::text)

Seq Scan on a large table is the PostgreSQL equivalent of type: ALL.

Understand the columns that matter

Ignore the wall of output and check four fields:

• type (MySQL) or the node name (PostgreSQL). ALL and Seq Scan mean every row is read. ref, eq_ref, range and Index Scan mean an index is doing work.
• key / Index Cond. If it says NULL or is missing, no index was chosen.
• rows / rows=. This is the optimiser's estimate of rows examined. A number close to the table size is a red flag.
• Extra / Filter. Using filesort, Using temporary and Filter on a large row count all mean extra work after the scan.

Add an index that matches the query

The query filters on status and sorts on created_at, so a composite index covering both removes the scan and the filesort.

CREATE INDEX idx_orders_status_created ON orders (status, created_at);

CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);

Re-run the same EXPLAIN. You want type: range or ref on orders, the new index named in key, a much smaller rows estimate, and no Using filesort.

Order composite index columns correctly

Column order decides whether the index is usable. Put equality conditions first, then range or sort columns.

-- WHERE status = ? AND created_at > ? ORDER BY created_at
CREATE INDEX idx_orders_status_created ON orders (status, created_at);

An index on (created_at, status) would not help this query, because the range on the second column breaks the left-to-right rule. If you also filter by customer_id, the useful order is (customer_id, status, created_at).

Check which indexes already exist before adding more:

SHOW INDEX FROM orders;

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders';

Every extra index slows down inserts and updates, so drop ones that no query uses.

Rewrite the query

Wrapping an indexed column in a function hides it from the optimiser.

-- index on created_at cannot be used
SELECT id, total FROM orders WHERE DATE(created_at) = '2026-01-01';

-- same result, index-friendly range
SELECT id, total FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02';

The same applies to implicit type conversion: comparing a VARCHAR column to an unquoted number, or joining columns with different collations, forces a scan. Fix the comparison rather than the index.

For deep pagination, LIMIT 100000, 20 still reads 100,020 rows. Keyset pagination avoids it:

SELECT id, total FROM orders
WHERE status = 'pending' AND created_at < '2026-01-01 12:00:00'
ORDER BY created_at DESC
LIMIT 20;

Measure real cost with EXPLAIN ANALYZE

Estimates can be wrong. EXPLAIN ANALYZE runs the statement and reports actual rows and timing.

EXPLAIN ANALYZE SELECT o.id, o.total FROM orders o WHERE o.status = 'pending' ORDER BY o.created_at DESC LIMIT 20;

EXPLAIN (ANALYZE, BUFFERS) SELECT o.id, o.total FROM orders o WHERE o.status = 'pending' ORDER BY o.created_at DESC LIMIT 20;

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'done' WHERE status = 'pending';
ROLLBACK;

Compare rows (estimate) with actual rows. A large gap usually means stale statistics — run ANALYZE TABLE orders; in MySQL or ANALYZE orders; in PostgreSQL.

Check it works

1. Re-run the original EXPLAIN and confirm the index appears in key or Index Cond.
2. Run EXPLAIN ANALYZE and compare execution time before and after.
3. Time the query end to end with mysql -e "..." or psql -c "..." and time, so client overhead is included.
4. Watch the slow query log for a day to confirm the statement no longer appears.
5. Check SHOW PROCESSLIST; or pg_stat_activity under load to make sure the query is not waiting on locks.

Troubleshooting

• type: ALL or Seq Scan on a large table — no usable index, or the optimiser rejected it. Check that the index columns match the WHERE and JOIN columns exactly, then run ANALYZE to refresh statistics.
• Index exists but is ignored — usually a function on the column, an implicit type conversion or a collation mismatch. Rewrite the predicate as a plain range comparison.
• Using filesort or Using temporary remains — the ORDER BY or GROUP BY does not match the index order. Add a composite index whose trailing column matches the sort direction.
• Fast in isolation, slow under load — the query is waiting on row locks or the buffer pool. Check SHOW ENGINE INNODB STATUS; for lock waits, or the deadlock troubleshooting guide.
• Plan changes between runs — statistics drift or parameter sniffing. Re-run ANALYZE, and test with the real parameter values your application uses.

Next steps

• Capture the statements worth tuning with the MySQL slow query log.
• Check whether the table itself is the problem with database and table sizes in MySQL and MariaDB.
• If you are still on PostgreSQL basics, start with installing PostgreSQL and creating your first database.
• When the query is fast but the disk is the bottleneck, tune disk I/O with mount options and swap.