WebTech1223411 logo WebTech1223411Web tech, read closely
Back-End

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.

A developer's monitor showing a database query plan next to a latency graph that drops sharply, in a dim office with a mechanical keyboard

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.

  1. 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.
  2. 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.
  3. 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.

KO
Kofi Oosterhuis

Kofi spent years keeping APIs and game servers alive through traffic spikes and the occasional bad deploy. He covers back-end design, scaling and networking, with a preference for boring systems that do not page anyone at night.

More posts by Kofi

More in Back-End

01doorganico 084goad 3hot8 4465456 after5internprogram ahandfulofblessings ahmadpc ahomcybersecurity allodialresources annodex antiquesandperiodcollectibles aotc asianworldkashmir beedlectrics bloggertemplatefree bluefeatheragency bodyweightsolution campaign08blog carfixadvisor catstreetcollective cbstvsports cnfanspurchasing committeeof500yearsofdignityandresistance covertopsdjs cs-kaiyuangame cuestacomunicaciontotal cxoutlet dancebetgame deskgearhub destinyvillastay detectmagazine di33 diamondsteelbuildings donmo dyanadropship emarsysfinance emimattl escapechan eskitarih evautoplay everythingweddings-orlando farmzone feedviewerapp foxstudiosscratch gamevui123 gleasongrade greetingsday hacklabasu haskell iw-kaiyun jagoanproperti jilidskripsi joltbusinessfinance juegodepokergratis juegospucca kaeru-festival latestmehndidesigns lestanzediottaviano litemagazines localcommunityhero lustrsazan mariashebets menswheels newwebstechs1 nikkit ootheday oxigenvpn plurfitness prefetial probudgetcarpetcleaning psychicmrsmartin pyaten qvantor rejoignezlogis rexzbot riselayouts ruchin rwxqsgd5 sakaltime shibiaowang shop-bot skrtips spotlesshaven strcoding tetapatrium topselller uneplanteartificielle vipertechcr wo-kygaming yamaha-mekar yeknava yoybuysheets zenunwa yamaha-mekar jilidskripsi ankertoto ankertoto ankertoto