An API endpoint that responds in 40ms during development and takes 4 seconds in production isn't usually a code problem. Far more often, it's an indexing problem that nobody noticed because the test database had a few hundred rows and the production database has a few million.
Here's what actually goes wrong, and how to catch it before your users do.
The Query That "Works Fine" Until It Doesn't
sql
SELECT * FROM orders WHERE customer_id = 4821 AND status = 'pending';
This query is instant on a table with 500 rows. On a table with 5 million rows and no index on customer_id or status, the database has to scan every single row to find matches. It still returns correct results - it just gets slower in direct proportion to table size, which means it can pass every test in a dev environment and still become a production incident six months after launch, right around the time the table crosses some invisible threshold.
This is the core trap: indexing problems don't show up as bugs. They show up as gradual degradation that's easy to dismiss as "the server needs more resources" long before anyone checks whether the query plan is doing a full table scan.
Missing Indexes Are the Obvious Case - Composite Indexes Are Where It Gets Interesting
Most developers know to add an index on a column they filter by frequently. The more common mistake is misunderstanding composite (multi-column) indexes.
sql
CREATE INDEX idx_customer_status ON orders (customer_id, status);
This index helps a query filtering on customer_id alone, or customer_id AND status together - but it does not meaningfully help a query filtering on status alone. Column order in a composite index matters, and getting it backwards is one of the most common reasons a team adds an index, sees no performance improvement, and concludes indexing "doesn't help" for their use case.
Over-Indexing Has Its Own Cost
The instinct after learning this lesson is often to add an index for every column that ever appears in a WHERE clause. That creates a different problem: every index has to be updated on every insert, update, and delete to the table. A table with ten indexes on it pays that write cost ten times over, which can turn a fast-writing table into a slow one - particularly painful for high-write tables like event logs or activity feeds.
The right number of indexes is the minimum set that covers your actual query patterns, not the maximum set that covers every theoretically possible query.
EXPLAIN Is Not Optional
Most database engines offer a way to see exactly how a query will be executed - EXPLAIN in PostgreSQL and MySQL, for instance. Running this on your slower endpoints before they hit production is one of the highest-leverage five-minute habits a backend developer can build. It tells you directly whether a query is using an index or falling back to a full table scan, instead of leaving you to guess based on response times alone.
sql
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 4821 AND status = 'pending';
If the output shows a sequential scan on a large table where you expected an index scan, that's your answer - and usually a five-minute fix, versus the multi-hour incident it becomes once it's a production emergency.
N+1 Queries Make the Problem Worse, Not Just Slower
A single unindexed query is bad. An unindexed query executed once per item in a loop - the classic N+1 pattern common in ORMs - multiplies that cost by however many items are in the result set. An endpoint returning 50 orders, each triggering a separate unindexed lookup for its line items, turns one slow query into 50 slow queries stacked in sequence. This combination is one of the most common root causes behind APIs that feel randomly slow under real usage but fast in every manual test.
Indexes Need Maintenance Too
Indexes aren't a set-it-and-forget-it fix. As query patterns evolve - new features, new filters, new sort orders - indexes that made sense at launch can become dead weight, and new query patterns can go unindexed because nobody revisited the schema after the initial build. Periodically reviewing actual production query patterns against your current indexes catches this drift before it becomes a performance regression nobody can explain.
A Practical Checklist
- Run EXPLAIN on any endpoint that's slower than expected before assuming it's an application-code problem.
- Check composite index column order against your actual query filters, not just which columns are involved.
- Watch for N+1 patterns in ORM-generated queries, especially in list endpoints.
- Periodically audit indexes against real production query logs, not just the queries you wrote at launch.
- Balance read performance against write cost - more indexes isn't automatically better.
The Takeaway
Most API performance problems that get blamed on "the framework" or "needing more servers" trace back to a handful of indexing mistakes that are genuinely simple to fix once you know where to look. It's the kind of detail that's easy to skip under deadline pressure and expensive to untangle later - which is exactly why teams that pair senior engineering judgment with AI-assisted development still keep a human reviewing query plans and schema design, not just the application logic sitting on top of them.
Anchor text used above: "teams that pair senior engineering judgment with AI-assisted development" → links to https://www.zoraz.net/
Top comments (0)