Sometimes you don't need a new index, but simply need to add an additional column or two to an existing index. I've gotten in the habit of looking at all existing indexes first to see if any of them should cover the query.

And yes it is necessary to know your ratio of reads to inserts/updates/deletes. In my case I have one table with millions of rows, and there is only one process doing the inserts & updates, while hundreds of processes read from this table. For this table the numerous indexes are more beneficial, but I don't add one until I've ruled out every other possibility. With only one process doing inserts, I have better control of how the data gets in the table, and I can set & adjust thresholds every so often.

And once in a while, you'll find that the only way to improve performance is to redesign the table layout. I did that just once, because I despise having to scan a table multiple times to get the data I need from it.