Problem Statement
A feed endpoint returns the last 50 posts for a user ordered by created_at DESC, id DESC. Design an index to avoid sorting and discuss why the second key matters.
Explanation
Create a composite index keyed by (user_id, created_at DESC, id DESC). The engine can seek on user_id and stream rows already in the required order. Adding id as a tiebreaker stabilizes order when timestamps collide and preserves index order across peers.
Returning the first 50 rows becomes a cheap ordered index scan with an early stop.
Code Solution
SolutionRead Only
CREATE INDEX idx_posts_feed ON posts(user_id, created_at DESC, id DESC);
Practice Sets
This question appears in the following practice sets:
