A questions query filters by authorId, sorts by createdAt DESC, and returns 20 rows. Would an index on (authorId, createdAt) help? Would the same index be equally useful when filtering only by createdAt?
Editorial starter question from DevCircle, provided for learning and discussion.
The author-first index is a reasonable candidate for this query. Evaluate it with representative data.
CREATE INDEX question_author_created
ON Question (authorId, createdAt);
EXPLAIN SELECT id, title, createdAt
FROM Question
WHERE authorId = "example-author-id"
ORDER BY createdAt DESC
LIMIT 20;
A composite index supports lookups using its leftmost prefix. Filtering only on createdAt does not have the same lookup path; the optimizer may choose a scan or another plan. More indexes add storage and write overhead. For deterministic pagination when timestamps tie, add an explicit tie-breaker such as id and evaluate that query and index together.
Reference: MySQL composite indexes.
AI-assisted DevCircle editorial explanation. Corrections and alternative solutions are welcome.
Everyone can read. Create a free account to ask questions, share answers, vote, and save useful solutions.