Why your database query is slow — and why it was fast on your own data

The symptom is recognisable: locally the page opened instantly. In production it thinks for eight seconds, and sometimes dies on a timeout. The code didn't change. Only the data did — there's more of it.
And here's what to grasp right away. The data grew a thousandfold, but the query got ten thousand times slower. The growth isn't linear — it falls off a step. The cause is almost always one of three, and the first covers most cases.
Measure first, don't guess
Before changing anything, ask the database to explain what it's doing. In Postgres and MySQL you prefix the query with EXPLAIN ANALYZE:
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;
It'll show the plan: how it searched and how long that took. You don't need to read the whole plan — look for two words.
Seq Scan(in MySQL,type: ALL) — "sequential scan". The database read the table in full.Index Scan— it found rows via an index, directly and precisely.
That's it. Now to the causes.
Cause 1: no index — the database reads the whole table
The most common by a wide margin.
What's happening. Without an index, a database can't "find" a row. It has no choice but to look at every row and test the condition. A hundred rows — imperceptible. A hundred thousand — it reads a hundred thousand, every single time. On every request from every user.
An index is like the alphabetical index at the back of a book. Without it you hunt for a word by flipping through every page. With it you open the index, see "page 287", and go there.
How to check. EXPLAIN ANALYZE shows Seq Scan on a large table — that's it.
How to fix. Create an index on the column you filter or join by:
CREATE INDEX idx_orders_user_id ON orders(user_id);
The rule that covers nearly everything: every column appearing in WHERE, JOIN or ORDER BY wants an index. Check your queries — odds are half of them have none.
Why isn't this done by default for every column? An index is a separate structure that must be updated on every write. Too many needless indexes slow down inserts and eat disk. So you add them deliberately, based on your actual queries.
Cause 2: N+1 — not one slow query, but five hundred fast ones
Second most common, and nearly invisible in code — which looks perfectly clean.
What's happening. You fetched a list of 100 orders. Then, in a loop, you fetched the user for each order. That's 101 queries instead of two. Each one is fast, 3 ms. But 101 round trips to the database adds a third of a second out of nowhere — and in production, where the database sits on another server, seconds.
It's especially easy to hit with an ORM: order.user.name inside a loop looks like reading a field, while under the hood it's a separate database query.
How to check. Turn on SQL logging and load the page once. If you see a hundred nearly identical queries differing only by id — that's N+1.
How to fix. Fetch everything in one query instead of looping:
- with an ORM — eager loading (
include, a relationalselect,prefetch_related— the name depends on the library); - in raw SQL — a
JOIN, orWHERE user_id IN (...)as a single query.
Cause 3: dragging needless data over the network
The most harmless, but constant.
What's happening. SELECT * takes every column, including the ones you don't need: long descriptions, base64 images, JSON fields weighing tens of kilobytes. For a list where you show only a name and a date, the database dutifully packs and ships all of it.
Same family: a query with no LIMIT. You display the twenty latest orders, but fetch all forty thousand and trim in code.
How to check. Look at the timings: the query itself is fast in the database (EXPLAIN is happy) yet the response takes ages to reach your app. That points at data volume.
How to fix. List columns explicitly and add a LIMIT:
SELECT id, created_at, total FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;
If a query is genuinely heavy and its result rarely changes, caching helps next — but only after you've cleared the first three causes. Caching a slow query hides the problem rather than solving it.
FAQ
How do I know it's time to add an index?
Not by table size, but by the query plan. EXPLAIN ANALYZE showing Seq Scan where you're looking up specific rows means it's time. It works on a thousand-row table too; you just won't feel the difference.
Can indexes make things worse?
Yes, on writes: every INSERT and UPDATE updates all of the table's indexes. For a typical app that reads far more than it writes, the gain outweighs that many times over. Trouble starts around a dozen indexes on one table.
Should I move to a "faster" database?
Almost never. In the overwhelming majority of cases it isn't the database that's slow, it's a query with no index — and migrating just moves the problem to a new home. EXPLAIN ANALYZE first, conclusions second.
The AI Architect 2027 course: step by step from computer basics to your own app and first customers. 543 short lessons on your phone.





