After working in ruby on rails for a number of years, I have found a number of performance related bottlenecks due to a lack of indexes on the database tables. I’ve noticed that most rails developers don’t write much SQL anymore, which is great, but also sometimes a problem. Back in the day, you really got your hands dirty in SQL, which made you really think of tuning the database. As relational databases are still around, I figured I’d give an overview on index basics.
I am going to summarize my findings here, and let those of you interested keep on reading through my empirical findings….
New Developer: When should I use an index?
Me: Any time the column appears in the WHERE, GROUP BY or ORDER BY clause.
1. if you add a column for a belongs_to (like t.integer :user_id) for belongs_to :user – make sure there’s an index
2. if your column will appear in a WHERE, GROUP BY or ORDER BY clause
3. try to use composite indexes to reduce the amount of indexes needed.
4. other than that – wait until your app is up and running, and the use some performance tools to identify a need for other indexes.
5. Generally – use a full text search engine like solr or sphynx if you are getting into full text searching. Even if your database supports it, the SQL needed doesn’t work the same way on all database vendors.