Planned lessonMID-012-L02

Composite indexes and column order

On This Lesson 4 topics

This lesson is planned. The outline below lists the topics it will cover. The full lesson has not been published yet.

In this lesson

choose the column order of a multi-column index.

The index is sorted by the first column, then the second within it, and so on.
It serves queries that use a leftmost prefix: (a, b) helps a and a, b, not b alone.
Put equality columns first, then the range or sort column.
Match the real predicates; query shape matters more than raw selectivity.
A matching index can also supply the sort order and avoid a sort step.

Draft outline details

sql

CREATE INDEX idx_orders_cust_created ON orders (customer_id, created_at DESC);
-- serves: WHERE customer_id = ? ORDER BY created_at DESC LIMIT 20

Will (customer_id, created_at) efficiently serve WHERE created_at > ? alone?

Show answer

No; the leading column is missing.

Apply this lesson with the module's lab, exercises and quiz.

Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.