Diagnosing slow database performance: slow query optimization with EXPLAIN

Read MySQL and PostgreSQL execution plans with EXPLAIN, find full table scans and filesorts, add the index the query actually needs, and confirm the speed-up.

  • Advanced
  • Linux VPS
  • 7 min read

By RunRacks Team · Published

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:

Terminal
ssh [email protected]
mysql -u deploy -p -h 127.0.0.1 shop
Terminal
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.

SQL
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:

Text
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:

SQL
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;
Text
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.

SQL
CREATE INDEX idx_orders_status_created ON orders (status, created_at);
SQL
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.

SQL
-- 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:

SQL
SHOW INDEX FROM orders;
SQL
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.

SQL
-- 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:

SQL
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.

SQL
EXPLAIN ANALYZE SELECT o.id, o.total FROM orders o WHERE o.status = 'pending' ORDER BY o.created_at DESC LIMIT 20;
SQL
EXPLAIN (ANALYZE, BUFFERS) SELECT o.id, o.total FROM orders o WHERE o.status = 'pending' ORDER BY o.created_at DESC LIMIT 20;
SQL
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

Was this guide helpful?

Still stuck?

Ask RunRacks AI for a quick answer, or open a ticket and our team will take a look at your server.

Control panels

CyberPanel: back up and restore a website

Create on-demand and scheduled website backups in CyberPanel, download the archives off the server, and restore a site from a backup when something breaks.

  • Intermediate
  • Linux VPS
  • 6 min read