Rails ActiveRecord Table Indexes - When should they be used?

Question

I have heard over and over that you should add indexes to any foreign key you will be doing joins on. I have also heard you should have indexes for fields you will do queries on. Does anyone have a fairly exhaustive list or set of guidelines around when and when not to add indexes?

There must be a size of table where it is inefficient to have indexes. There must be a limit to the number of indexes per table, or why not add an index to every column?

Any resources you can suggest would be very helpful.

Thanks in advance!

Answer 1

why not add an index to every column

Indexes speed up queries but can slow down inserts and updates. Finding the sweet spot will depend in part on whether your application is likely to do more queries (e.g. a library catalog) or more updates (e.g. an auditing application or log).

Answer 2

Put an index on anything you're likely to join on or sort by. In most cases, it's better to err on the side of more indexes than less, simply because most databases involve more reads than writes, especially web applications. If you were doing financial transactions, it'd be different, but this is a Rails application.