Slow queries rarely begin as a database problem. They begin as a product feature that works beautifully with a few rows, then quietly becomes the busiest path in the system. A customer list gains filters. An API endpoint adds sorting. A dashboard starts grouping activity. The SQL still looks reasonable, but response times become unpredictable as data grows.

Database indexes are how a system turns that uncertainty into a deliberate architectural choice. They are not a switch to apply to every column. Each index is a data structure with a cost: extra storage, more work during writes, and another path the optimizer must evaluate. Used with intent, indexes make important queries reliably fast while keeping the operational cost visible and manageable.

Index the queries your system actually serves

An index should support an access pattern, not merely a column that seems important. Before adding one, identify the complete query shape: its filtering conditions, joins, sort order, selected fields, and expected result size.

Consider an endpoint that shows recent paid orders for one account:

SELECT id, total_cents, created_at
FROM orders
WHERE account_id = ?
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

An index on account_id is better than no index, but it may still leave the database filtering by status and sorting a potentially large set of rows. A composite index aligned with the query is usually more useful:

CREATE INDEX idx_orders_account_status_created
ON orders (account_id, status, created_at DESC);

The point is not that every query needs three columns. The point is that the index reflects the work the database must avoid: locating the account’s paid orders and reading them in the requested order.

Column order is part of the design

Composite indexes have an order, and that order matters. A common practical model is to put equality filters first, then columns used for range conditions or ordering. In the example above, account_id and status are equality conditions, while created_at supports the requested order.

That model is a starting point, not a substitute for checking the execution plan. Database engines differ, and a query’s data distribution can change which plan is best. An index beginning with account_id is useful for queries constrained by account, but it is generally not a direct replacement for an index beginning with status when the query filters only by status.

Make predicates index-friendly

An otherwise suitable index can be defeated when the query transforms the indexed column. For example, this condition often prevents a normal timestamp index from being used efficiently:

WHERE DATE(created_at) = '2026-10-02'

Express the same requirement as a range instead:

WHERE created_at >= '2026-10-02 00:00:00'
  AND created_at < '2026-10-03 00:00:00'

The range version lets the database navigate directly to the relevant part of an index on created_at. The same principle applies to implicit type conversions, leading-wildcard searches such as LIKE '%term', and calculated expressions in predicates. If the desired query genuinely requires a transformed value, consider a database feature designed for that purpose, such as an expression or generated-column index, after confirming it is supported by the chosen database.

Use execution plans as evidence

Indexing by intuition creates both missed opportunities and redundant indexes. The dependable workflow is to capture the real query, run the database’s plan inspection command, add or adjust an index, then inspect the plan and measure the endpoint again.

EXPLAIN SELECT id, total_cents, created_at
FROM orders
WHERE account_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

An execution plan is not a pass/fail report. Read it as an explanation of work. Look for full table scans on large tables, expensive sorts, unexpectedly large row estimates, and join steps that process far more rows than the request needs. Also remember that a plan showing an index does not automatically mean the query is healthy. It may still fetch too many rows, join inefficiently, or return a payload that the application does not need.

Test with representative data. An index that appears unnecessary on a development database with hundreds of rows can become essential with millions. Conversely, a low-selectivity condition such as a two-state flag may not be valuable by itself because it matches a large share of the table. It can still be useful inside a composite index that narrows a more specific query.

Every index makes writes more expensive

When a row is inserted, updated, or deleted, the database must maintain every affected index. A table receiving frequent writes can become slower and larger when indexes are added indiscriminately. This is especially relevant for event logs, queues, session tables, and high-volume API ingestion paths.

That trade-off should shape the design:

• Keep primary keys and indexes required for critical reads.
• Remove duplicate or unused indexes after verifying they are not supporting important workloads.
• Avoid indexing every optional filter before knowing that users or services use it at meaningful scale.
• Review indexes when a write-heavy table changes shape or traffic pattern.
• Consider archival, partitioning, or separate read models when a single table is serving incompatible workloads.

Foreign-key columns often deserve special attention. Joins and parent-row changes can require efficient access to child rows, but the exact indexing behavior and automatic indexes vary by database. Treat the schema definition and the actual query plans as the source of truth rather than assuming a foreign key has created the needed index.

Design indexes with API contracts in mind

API design and indexing are closely linked. A flexible endpoint that allows arbitrary filtering and arbitrary sorting can create an unbounded set of query patterns. No small, maintainable index set can optimize every combination.

Set deliberate limits. Offer a defined set of filter fields, a small set of sortable columns, sensible pagination, and explicit defaults. For a large collection, keyset pagination is often more stable than deep offset pagination because it continues from a known sort position rather than requiring the database to skip an ever-growing number of rows.

SELECT id, created_at, total_cents
FROM orders
WHERE account_id = ?
  AND created_at < ?
ORDER BY created_at DESC
LIMIT 50;

This query can pair naturally with an index beginning with account_id and created_at. To make pagination deterministic when timestamps can match, include a stable tie-breaker such as id in both the order and cursor condition.

Deploy changes as operational changes

An index migration is not merely a schema edit. On a large or busy table, building an index can consume substantial resources and may affect concurrent work depending on the database engine, version, and migration method. Rehearse the change against representative data, understand the locking behavior of the chosen operation, and monitor the database during rollout.

Make rollback thinking concrete. A failed application deployment may need an index left in place because removing it could be just as disruptive as creating it. Schema changes need release planning that acknowledges this reality: deploy compatible application code, build or validate the index safely, then retire old paths only when the new behavior is established.

Predictability is the real performance feature

The best index is not the most sophisticated one. It is the one that makes a valuable, well-understood query consistently inexpensive without imposing needless cost elsewhere. Start from a real request, inspect the plan, model the read and write trade-off, and keep the API contract narrow enough that the database can honor it.

That discipline turns indexing from reactive tuning into architecture. Users experience steadier response times, operators get fewer surprises, and the codebase gains a performance model that can grow with the data instead of racing behind it.