When an application slows down as data grows, the database is the usual cause. The fix is often one well-chosen index, which can turn a query from seconds into milliseconds. This guide explains what an index is, when to add one and how to prove it helps.
What an index does
Without an index, the database reads every row in a table to find matches. That is a full scan, and it gets slower as the table grows. An index is a separate sorted structure, usually a B-tree, that lets the database jump to matching rows directly, like the index at the back of a book.
The cost of indexes
Indexes are not free. Each one uses storage and must be updated on every insert, update and delete. A table with ten unused indexes writes slower and wastes memory. Add indexes for queries you actually run, and remove those nobody uses.
Which columns to index
- Columns in frequent
WHEREfilters, such asuser_idorstatus. - Foreign keys used in joins.
- Columns used in
ORDER BYtogether with a filter, so the database can return rows already sorted. - Fields with high selectivity, meaning many different values, like email. An index on a yes/no column rarely helps.
Compound indexes and column order
A compound index covers several columns. Order matters: put equality filters first, then range or sort columns. For a query that filters by user_id and sorts by created_at, an index on (user_id, created_at) serves both. The same index does not help a query that filters only on created_at, because the database can use only the leading columns.
Read the query plan
Never guess. Ask the database how it runs the query:
- PostgreSQL and MySQL:
EXPLAIN ANALYZE SELECT ... - MongoDB:
.explain("executionStats")
Look for a full table scan (Seq Scan or COLLSCAN), a large gap between rows examined and rows returned, and a separate sort step on a big result. After adding an index, run the plan again and confirm it switched to an index scan (IXSCAN) and examines far fewer rows.
Common reasons an index is ignored
- A function on the column, such as
WHERE LOWER(email) = ..., unless the index is built on that expression. - A leading wildcard, as in
LIKE '%term'. - A type mismatch, such as comparing a string column to a number.
- Low selectivity, where the planner decides a scan is cheaper.
- Stale statistics. Run
ANALYZEafter large data changes.
Partial and covering indexes
A partial index covers only rows that match a condition, for example WHERE status = 'active', and stays small. A covering index includes every column a query needs, so the database never reads the table itself. Both help on hot paths with a narrow query shape.
Find the slow queries first
Turn on the slow query log, or use pg_stat_statements in PostgreSQL, and sort by total time. A query that runs 50,000 times a day at 40 ms matters more than one that runs twice at 3 seconds. Fix the top three and measure again.
A safe workflow
- Capture the slow query and its plan.
- Create the index in a staging copy with realistic data.
- Compare plans and timings.
- Build it in production with the online option, such as
CREATE INDEX CONCURRENTLY, to avoid locking writes. - Watch latency and write speed for a few days.
Pagination deserves a note
Large OFFSET values force the database to read and discard rows. For long lists, use keyset pagination: filter with WHERE id < last_seen_id ORDER BY id DESC LIMIT 20 and let an index do the work. For wider load advice, read Scaling Node.js Applications.
An example from a billing system
Suppose an invoices table holds 800,000 rows and the list screen filters by business, status and date, then sorts by newest. Without an index the query reads every row and takes seconds. A compound index on (business_id, status, created_at DESC) lets the database jump straight to the right slice and return the first 20 rows in a few milliseconds. The query did not change, only the way the database finds the data. Always add the index in staging first and compare the plans before and after.
Signs your database needs attention
- List screens get slower as the customer's data grows, while small accounts stay fast.
- CPU on the database server is high but traffic is normal.
- The same few queries dominate the slow query log.
- Reports and exports time out on large date ranges.
Why multi-tenant business apps feel this first
Software that serves many businesses from one database, as our Lena Dena business platform does, filters almost every query by the business. That makes the tenant column the first column of most indexes, together with the field you filter or sort on. The payoff is steady speed for invoicing, inventory and reports even when one customer has years of data. Chat features need the same care: a WhatsApp bot that looks up a customer or an order by phone number, like our WhatsApp automation, has to answer in a second or two, so the phone field needs an index.
If your application slows down under real traffic, our team audits queries and infrastructure. See our backend services or ask for a performance review.