I recently sped up a complicated query by an order of magnitude by giving SQLite a good index to work with. Results like this make me wonder if I should index a lot of other fields that are commonly used for JOINs or ORDER BY clauses. But I don't want to get overzealous and have it backfire on me: I assume there must be some reasons not to create indices, or every field would be indexed by default.
I'm using SQLite in this case, but of course DBMS-agnostic advice is welcome as well.
Indexes slow down inserts and updates (which can become a really serious issue with locking) and cost disk space. That's pretty much it.