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.
Indexes use up disc space to store, and take time to create and maintain. Unused ones don't give any benefit. If there are lots of candidate indexes for a query, the query may be slowed down by having the server choose the "wrong" one for the query.
Use those factors to decide whether you need an index.
It is usually possible to create indexes which will NEVER be used - for example, and index on a (not null) field with only two possible values, is almost certainly going to be useless.
You need to explain your own application's queries to make sure that the frequently-performed ones are using sensible indexes if possible, and create no more indexes than required to do that.
The cost of an index in disk space is generally trivial. The cost of additional writes to update the index when the table changes is often moderate. The cost in additional locking can be severe.
It depends on the read vs write ratio on the table, and on how often the index is actually used to speed up a query.
In order to test your particular application you can put "EXPLAIN QUERY PLAN" in front of any query you run and check the results. It will show you where it is or is not using indexes.
That way you can determine where you could use more indexes and where they would not make a difference.
I use SqliteSpy to hand test query's that seem to be causing trouble.