Medium Writes & indexes Premium

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_idtitlealbum_idduration_seconds
1Come Together1259
2Something1182
3Here Comes the Sun1185
About the music sample database

Practice on your iPhone

Query Dojo for iPhone has 85 interview questions, timed mock interviews and a tile builder, so you can write queries without a keyboard.

Get the app

Keep going