Indexing Strategy in Database Design

An index is a data structure the database maintains alongside a table specifically to make certain lookups fast, at the cost of extra storage and slightly slower writes (since every index must also be updated whenever the underlying row changes). Indexing strategy means deciding, deliberately, which columns and column combinations deserve an index based on how the application actually queries the data — not by indexing every column defensively, and not by leaving performance-critical queries unindexed and hoping the table stays small.

A schema can be perfectly normalized and fully constraint-correct and still perform badly in production if its indexes don't match its real query patterns. Indexing is where schema design meets actual, measured application behavior.

Why it matters

Indexes decide whether a query takes milliseconds or seconds, and they are designed from the queries the application actually runs, not from guesses. An index on the wrong columns, or with the columns in the wrong order, is dead weight that slows every write and helps nothing.

Index Around Real Query Patterns, Not Guesses

The right index for a table depends entirely on what WHERE clauses, JOIN conditions, and ORDER BY clauses the application actually runs against it. A foreign key column used constantly in joins (orders.customer_id) almost always deserves an index; a column that is only ever read as part of a full row fetch by primary key usually does not need one of its own. The starting point for indexing decisions should be the list of real queries gathered during the requirement-to-schema workflow, not a blanket rule like "index every column."

Composite Indexes and Column Order

A composite (multi-column) index is built on more than one column together, and the order of those columns matters enormously: a composite index on (customer_id, placed_at) can efficiently serve a query filtering on customer_id alone, or on customer_id and placed_at together, but it cannot efficiently serve a query that filters on placed_at alone, because the index is physically sorted by customer_id first. The general rule is to put the column used for equality filtering (customer_id = ?) before the column used for range filtering or sorting (placed_at > ? or ORDER BY placed_at).

The Cost Side of Indexing

Every index speeds up the reads it matches, but slows down every INSERT, UPDATE, and DELETE on that table slightly, because the index must be updated too, and it consumes additional disk space. A table with ten indexes "just in case" pays that write cost ten times over on every insert, for indexes that may serve queries the application never actually runs. Indexing strategy is therefore a genuine trade-off decision, revisited as query patterns are learned or change, not a one-time exhaustive pass.

• Index: foreign key columns used in joins, columns in frequent WHERE equality/range filters, columns used for sorting on hot pages.
• Be selective about indexing: low-cardinality columns (like a boolean) rarely benefit from a standalone index.
• Composite index order: equality-filtered columns first, then range-filtered or sorted columns.

Example

-- Known application query: "show a customer's orders, most recent first"
-- SELECT * FROM orders
-- WHERE customer_id = ?
-- ORDER BY placed_at DESC;

CREATE TABLE orders (
    id           BIGINT PRIMARY KEY,
    customer_id  BIGINT NOT NULL REFERENCES customers(id),
    placed_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status       VARCHAR(20) NOT NULL DEFAULT 'pending'
);

-- customer_id first (equality filter), placed_at second (sort/range)
CREATE INDEX idx_orders_customer_placed_at ON orders (customer_id, placed_at DESC);

-- This index also serves "customer_id = ? alone" efficiently, but does
-- NOT efficiently serve a query that filters on placed_at without
-- customer_id, since the index is sorted by customer_id first.

The known query — filter by customer_id, sort by placed_at descending —
can be satisfied directly from the index without a separate sort step
or a full table scan. A different query that only filters on placed_at
(e.g. "all orders placed today across all customers") would need its
own separate index on placed_at alone to be efficient.

Exercise

The application's most frequent query lists one customer's orders, newest first. Create a composite index named idx_orders_customer_created designed for exactly that query, with the columns in the right order. Then run the query for customer 1, returning the two newest order ids.

Expected output: id, 3, 1 (one per line)

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, created_at DATE NOT NULL);
INSERT INTO orders (id, customer_id, created_at) VALUES
  (1, 1, '2026-02-10'),
  (2, 2, '2026-02-11'),
  (3, 1, '2026-03-05'),
  (4, 1, '2026-01-20');

-- TODO: the composite index for "orders of one customer, newest first"

-- TODO: the ids of customer 1's two newest orders

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, created_at DATE NOT NULL);
INSERT INTO orders (id, customer_id, created_at) VALUES
  (1, 1, '2026-02-10'),
  (2, 2, '2026-02-11'),
  (3, 1, '2026-03-05'),
  (4, 1, '2026-01-20');

CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at);

SELECT id
FROM orders
WHERE customer_id = 1
ORDER BY created_at DESC
LIMIT 2;

Quick quiz

Interview questions