I got asked this in an interview years ago, and I've asked it from the other side of the table since. I like it because the lazy answer ("index everything") is wrong, and the real skill is knowing where to look before you touch anything. So here's the whole loop: how I spot a column that needs an index, how I prove the index actually helped, and what it costs me to add one.
Which columns actually need one
The candidates are columns you filter, sort, or join on. In SQL terms, anything in a WHERE, ORDER BY, JOIN, or GROUP BY on a table that's big or growing.
A few I always check first:
Foreign keys, like user_id and order_id. In Rails these don't get an index automatically when you add the reference unless you ask for one, and they get hammered by association lookups and joins. This is the missing index I find most often.
