SQL interview question: Composite Index on Two Columns
The Two-Column Index
The player constantly asks "tracks on album X, ordered by duration". One composite index can serve both the filter and the sort.
Create an index named exactly idx_tracks_album_duration on tracks covering album_id first, then duration_seconds.
Note: Column order matters and is verified. Rolled back afterwards.
This challenge changes data. Your statement runs on a private copy of the database, then we check the resulting table.
Tables
tracks 40 rows
- track_id integer PK
- title text
- album_id integer → albums
- duration_seconds integer
| track_id | title | album_id | duration_seconds |
|---|---|---|---|
| 1 | Come Together | 1 | 259 |
| 2 | Something | 1 | 182 |
| 3 | Here Comes the Sun | 1 | 185 |