What actually happens in a database index (and why half of them do nothing)

Same query. Same table. Same million rows. One day it takes 4 seconds. The next day, 4 milliseconds. Nothing changed in the data. The only thing that changed was one line — you added an index. Four se...