Your Database Is Not Slow, Your Queries Are

When a system slows down, the database gets the blame and the hosting plan gets the upgrade. Usually the database is doing exactly what it was asked, quickly. It has just been asked for far more work than anyone meant. A bigger server finishes that work sooner; it does not make it go away.

Here is the first hour I would spend with a slow system. You can do most of it yourself with read access and a little patience for query plans. Each example says whether it is PostgreSQL or MySQL.

Minutes 0 to 15: find what costs the most

Do not start with the query someone complained about. Start with what the database spends its time on, sorted by total time. A query that takes a few milliseconds but runs on every page view can cost more than the report everyone hates.

In PostgreSQL that list comes from pg_stat_statements. It has to be in shared_preload_libraries and created once with CREATE EXTENSION; many managed hosts have it on already.

-- PostgreSQL 13 or later (older versions say total_time, mean_time)
SELECT calls,
       round(total_exec_time::numeric) AS total_ms,
       round(mean_exec_time::numeric, 1) AS mean_ms,
       left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

In MySQL, turn on the slow query log:

-- MySQL: log anything slower than half a second
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;

The new threshold applies to new connections, and on shared hosting you may need to ask the host. Each log entry shows Rows_examined next to Rows_sent: a query that examines a million rows to send twenty is your first suspect. mysqldumpslow groups the log by query shape, and on MySQL 5.7 or later the sys.statement_analysis view gives a ranked list without a log at all.

Minutes 15 to 25: read the plan

Take the top three and ask the database how it runs them. In PostgreSQL that is EXPLAIN (ANALYZE, BUFFERS) before the query; in MySQL 8.0.18 or later, EXPLAIN ANALYZE. Both actually execute the statement, so wrap an UPDATE or DELETE in a transaction and roll it back.

Look for four things. A sequential scan on a big table (Seq Scan in PostgreSQL, type ALL in MySQL’s plain EXPLAIN), especially with a large “Rows Removed by Filter”. Estimated rows wildly different from actual rows, which means stale statistics (run ANALYZE, or ANALYZE TABLE in MySQL). A sort spilling to disk. And a loop node that runs thousands of times. Read from the most indented line outwards, because that is where the work starts.

Minutes 25 to 50: the usual culprits

Most slow queries turn out to be one of five things.

A missing index, or the wrong one

-- PostgreSQL or MySQL
SELECT id, total FROM invoices
WHERE customer_id = 4821
ORDER BY created_at DESC
LIMIT 20;

-- Equality column first, sort column second
CREATE INDEX idx_invoices_customer_date ON invoices (customer_id, created_at);

An index on (created_at, customer_id) looks similar and helps far less, because column order matters. And do not answer every slow query with a new index: each one slows down every insert and update.

A function wrapped around an indexed column

-- PostgreSQL: a plain index on email is ignored here
SELECT id FROM customers WHERE lower(email) = 'accounts@example.com';
-- Index the expression instead
CREATE INDEX idx_customers_email_lower ON customers (lower(email));

MySQL has a sneakier version, because nothing in the query looks like a function. Compare a VARCHAR column with a bare number, as in WHERE invoice_no = 10457, and MySQL converts every row and cannot use the index for the lookup. Quote the value.

SELECT * on a wide table

The cost is not the typing. It drags every column across the network, including the notes, the JSON and the attachment the list page never shows, and it stops the database answering from an index alone.

-- PostgreSQL 11 or later
SELECT id, number, total FROM invoices WHERE customer_id = 4821;
CREATE INDEX idx_invoices_customer_cover ON invoices (customer_id) INCLUDE (id, number, total);

In MySQL an index on (customer_id, number, total) does the same job, because InnoDB keeps the primary key in every secondary index.

N+1 queries from an ORM

In pg_stat_statements this shows up as one tiny query with an enormous call count. In the log it looks like this:

-- Either database: one query for the list, then one per row
SELECT id, customer_id, total FROM orders WHERE status = 'open' ORDER BY id LIMIT 50;
SELECT name FROM customers WHERE id = 17;
-- ...49 more

-- One query instead
SELECT o.id, o.total, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'open' ORDER BY o.id LIMIT 50;

In the ORM the fix is usually one call: with() in Laravel, select_related() in Django, includes in Rails.

Result sets with no limit

A report that loads a whole table into PHP and filters it there. An export with no WHERE. Pagination by OFFSET, which reads and discards every row before the page it returns. Filter in the query, limit anything a screen displays, and page by key:

-- PostgreSQL or MySQL: reads 40,020 rows to return 20
SELECT id, number, total FROM invoices ORDER BY id LIMIT 20 OFFSET 40000;

-- Keyset: start after the last id shown
SELECT id, number, total FROM invoices WHERE id > 40020 ORDER BY id LIMIT 20;

The OFFSET version gets slower the further someone pages. The keyset version costs the same on page one and page two thousand.

The last ten minutes: write it down

Fix one thing, run the plan again and compare the timing with what you recorded at the start. A fix you have not measured is a guess. Then write the list: each slow query, its daily cost (calls times mean time), the cause, the fix and the risk of the fix. Ranked by cost, that list is worth more than the hour of tuning, because it tells you what to do next week.

Or send it to me

If the hour raised more questions than it answered, or you would rather not run EXPLAIN ANALYZE on production yourself, here is an offer I mean literally: send me a slow query and its plan. You will get an opinion, not an invoice. If it turns into more, the work starts the same way this hour did: a written read of the schema, the queries and the indexes before anything is touched, and the slow queries ranked by what they are costing you, then fixed in that order.