How Database Indexes Speed Up Slow API Queries
An endpoint that returned in 40 ms when the table had 10,000 rows now takes 3 seconds at 4 million, and nothing in the code changed.

An endpoint that returned in 40 ms when the table had 10,000 rows now takes 3 seconds at 4 million, and nothing in the code changed. Nine times out of ten the cause is a query that scans the whole table because the right database indexes do not exist. The fix is often a single line of SQL, but picking the wrong index can make writes slower without helping reads at all.
This guide explains how indexes actually work, how to find the query that needs one, how to design composite indexes that match your API's filters, and when adding another index is the wrong answer.
What database indexes do under the hood
Without an index, a query like "find orders where customer_id is 812" forces the database to read every row and check each one. That is a sequential scan, and its cost grows in a straight line with table size. Double the rows, double the time.
An index is a separate, sorted structure, usually a B-tree in PostgreSQL and MySQL, that maps column values to row locations. Looking up a value in a B-tree takes a handful of page reads even with hundreds of millions of rows, because each level of the tree narrows the search by a factor of a few hundred. A table with 50 million rows typically needs a tree only three or four levels deep.
The trade off is that every insert, update or delete on an indexed column also has to update the index. Each index adds write cost and disk space. That is why "index everything" is not a strategy.
Find the slow query before adding anything
Guessing which column to index wastes time. Measure first.
- Turn on slow query logging. In PostgreSQL, set log_min_duration_statement to 200 ms. In MySQL, enable the slow query log with long_query_time set to 0.2. Let it run through a busy period.
- Group by query shape. Tools such as pg_stat_statements show total time per normalized query. The query that runs 50,000 times a day at 80 ms usually matters more than the one that runs twice at 4 seconds.
- Run EXPLAIN ANALYZE on the worst offender. Look for "Seq Scan" on a large table, or a "rows removed by filter" figure in the millions. Those are the signs an index would help.
Also check whether the slowness is really one query or hundreds of small ones. A list endpoint that fires a query per item will stay slow no matter how well indexed it is, which is the pattern covered in how N+1 queries slow an API.
Designing composite indexes that match your API
Most API endpoints filter on more than one column. A typical example: list a customer's orders with a given status, newest first. The query filters on customer_id and status and sorts by created_at.
A composite index on (customer_id, status, created_at) serves that query almost perfectly. The database jumps to the customer, narrows to the status, and reads rows already in date order, so no separate sort step is needed. Column order matters a great deal here. The rules I use:
- Equality filters first. Columns compared with "equals" go at the front.
- Range filters or sort columns last. Dates, prices and anything compared with "greater than" go at the end.
- Leftmost prefix rule. An index on (customer_id, status, created_at) also helps queries that filter only on customer_id, but not queries that filter only on status.
Sorted indexes also make pagination cheap. Cursor based pagination relies on an index that matches the sort order exactly; without it, every page triggers a full sort. We compare the approaches in API pagination with cursors vs offsets.
Covering indexes for hot endpoints
If an endpoint reads only a few columns, you can add them to the index so the database never touches the main table. In PostgreSQL that is the INCLUDE clause; in MySQL, adding the columns to the index itself does the same. On a hot endpoint serving 2,000 requests per second, removing that extra table lookup can cut median latency by 30 to 50 percent. Use this sparingly, since covering indexes are larger.
Why adding more indexes is often the wrong fix
When an API slows down, the reflex in many teams is to add an index for every column that appears in a WHERE clause. I push back on that, for three reasons.
First, write amplification. A busy events table with eight indexes does eight extra writes for every insert. I have seen ingestion throughput drop by more than half after a well meaning sprint of index additions, with no measurable read improvement because the planner ignored most of them.
Second, the planner only uses an index when it is selective. Indexing a boolean like is_active, where 95 percent of rows are true, rarely helps; the database correctly decides a scan is cheaper. A partial index, such as one on orders where status is "pending", is usually far better for that case.
Third, many "slow query" problems are not about indexes. Functions applied to an indexed column, like lowercasing an email in the WHERE clause, stop the index from being used. Implicit type casts, comparing a text column to a number, do the same. Fixing the query is cheaper than adding a second index.
Before adding one, check the existing ones. PostgreSQL's pg_stat_user_indexes shows how often each index is scanned. An index with zero scans after a month is pure write cost and can usually be dropped.
Rolling out an index on a live table
Creating an index on a large table can lock writes for minutes. On PostgreSQL, use CREATE INDEX CONCURRENTLY, which takes longer but lets traffic continue. On MySQL 8, most index builds are online by default, but check with ALGORITHM=INPLACE and LOCK=NONE so the statement fails rather than locks if it cannot run online. Build during a quiet period, watch replication lag, and keep the slow query log on afterward to confirm the plan actually changed. If latency is still high after the index is in place, look at whether the service is starved for connections, which is where database connection pooling comes in.
Frequently asked questions
Do database indexes slow down inserts?
Yes. Every index on a table must be updated on each insert, update or delete that touches its columns. One or two well chosen indexes cost little, but a table with many indexes can see noticeably lower write throughput.
How do I know if a query is using an index?
Run EXPLAIN or EXPLAIN ANALYZE on the query. The output names the index being used, such as "Index Scan using orders_customer_idx". If you see a sequential scan on a large table instead, the index is missing or the planner decided not to use it.
How many indexes should a table have?
There is no fixed number, but most tables in a typical API need between two and five. Each one should serve a real, frequent query that you have measured. Review usage statistics every few months and drop indexes that are never scanned.
More in Back-End
Back-End
How to Run Database Migrations Without Locking Tables
The migration itself took 41 milliseconds. The outage lasted nine minutes.
Back-End
How Graceful Shutdown Stops Deploys From Dropping Requests
Every deploy at a job I had before this one left a small notch in the error graph.
Back-End
How to Deliver Webhooks Reliably With Retries and Signing
A partner emails to say they never received the order.created event from last Tuesday.