Should I be adding a database index for every field I use find on?

Question

I have quite a few fields, in a few different models, upon which I perform searches using Model.find_by_xxx

Should I be adding a database index for every field I use find on?

Answer 1

Yes, probably. The cost of an index is that insert/update/delete statements are slower, since they now need to change the index also. The more indexes on a table, the longer it takes for insert/update/delete operations. So if you're mostly just reading the data then the indexes are a clear win.

The other downside to having many indexes is the disk space. You're basically making another copy of that column for the index. But usually that's a secondary consideration.

Answer 2

Not necessarily... it depends. How many rows do you expect to have in each table? How often are they updated? How many fields in each table do you search? Is the database likely to be used primarily for reporting, or primarily as part of an operational data entry system?