Start with the question
Suppose an order screen lists a customer's recent purchases. The application filters by customer and sorts by creation time. An index should support that access pattern.
SELECT id, created_at, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;
This example assumes an existing orders table with those columns. Try changes on a representative development copy before creating indexes on a production table.
Put the equality key first
A candidate index is:
CREATE INDEX orders_customer_created_idx
ON orders (customer_id, created_at);
The index groups entries by customer, then orders entries within each customer by creation time. That layout aligns with the query's filter and ordering.
The same index can also help a lookup on customer_id alone. A lookup using only created_at has a different access pattern; do not assume it gets the same benefit.
Inspect instead of guessing
EXPLAIN
SELECT id, created_at, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;
Inspect the chosen key, estimated rows, and extra operations. If the optimizer selects a different plan, consider table size, statistics, selectivity, and competing indexes.
There is no universal rule to put the most selective column first. Equality predicates, range conditions, ordering, grouping, and the rest of the workload all matter.
Watch the write cost
Every additional secondary index takes storage and must be maintained when rows change. A large collection of overlapping indexes can make writes more expensive and complicate tuning.
Before adding an index, record which queries it is intended to support. Afterward, compare query behavior and monitor write latency and storage growth.
A useful exercise
Compare the original query with a report that filters only by created_at. Write down why the two access patterns differ before proposing another index. This habit turns index design into a workload decision instead of a checklist.