In a composite index on (user_id, created_at), which query pattern is most efficiently served?

In a composite index on (user_id, created_at), which query pattern is most efficiently served?

• A. Queries filtering by user_id and optionally sorting by created_at.
• B. Queries filtering only by created_at.
• C. Queries filtering by a column not in the index.
• D. Any query will use the full index regardless of prefix.

Answer: (A)

A composite index on (user_id, created_at) is ordered first by user_id, then within each user_id by created_at. This arrangement means the index is most useful when you constrain the leading column. A query that filters by user_id can quickly locate the relevant rows, and if you also care about the results in created_at order, the index can deliver them in that order without an extra sort. That’s why filtering by user_id and optionally sorting by created_at is the most efficient use of this index. Filtering only by created_at doesn’t leverage the leading user_id, so the index isn’t as effective for quickly narrowing to the desired rows. Filtering by a column not in the index can’t use the index at all, leading to a full scan. And the idea that every query will use the full index regardless of prefix isn’t true; index usefulness depends on how the query matches the index’s leading columns.

A composite index on (user_id, created_at) is ordered first by user_id, then within each user_id by created_at. This arrangement means the index is most useful when you constrain the leading column. A query that filters by user_id can quickly locate the relevant rows, and if you also care about the results in created_at order, the index can deliver them in that order without an extra sort. That’s why filtering by user_id and optionally sorting by created_at is the most efficient use of this index.

Filtering only by created_at doesn’t leverage the leading user_id, so the index isn’t as effective for quickly narrowing to the desired rows. Filtering by a column not in the index can’t use the index at all, leading to a full scan. And the idea that every query will use the full index regardless of prefix isn’t true; index usefulness depends on how the query matches the index’s leading columns.