Rails 3 Database Indexes and other Optimization

Question

I have been building rails apps for a while now, but unfortunately for me, none of my apps have had a large amount of data or traffic. But now I have one that is gaining steam. So I am diving in head first into scaling and optimizing my app.

It seems the first and easiest step to do this is with database indexes. I've got a good huge list of indexes that should cover pretty much all of my queries, but when I added them to my database via migrations it only took a few seconds to add them. For some reason I thought they would have to go through all of my entries (of which there are thousands) and index them.

Does this mean my indexes haven't been applied to my already existing data? Will they only be added to new entries?

Additionally, I am looking into other scaling solutions, such as memcached, and all around slimming down my queries, etc.

If anyone can point me to some good resources for optimizing my rails 3 app I would greatly appreciate it!

Thanks!

EDIT:

Thanks for all the great answers regarding database indexes! What else should I be looking at in terms of optimizing and scaling my app? Memcached? What has the best performance boost/effort ratio in terms of optimization?

Answer 1

It is always a good idea to add index's to your all ID's and data you 'find_by' on more then a few occasions e.g. email_address. Likewise you can safely assume that ID will never go into negative, so making ID columns Unsigned will benefit in the long run. Speak to any DBA (Database Administrator) and they will, more times than not tell you to do this.

Currently you most likely have something like this for all your ID Columns...

t.integer :column_name, :null => false

or...

t.references :column_name, :null => false

By simply changing this to...

t.column :column_name, 'integer unsigned', :null => false

You will see a tiny increase.

Index's are simple...

add_index :reviews, [:column_id, :column_type] # Polymorphic
add_index :reviews, :column_id # Standard

The Rails API should give you all you need to know.

Peepcode have a really get tutorial video that was a great insight to me and well worth the $12 and 37 minutes of your time. There are Gems like MetaWhere which may be able to help you as well.

Most importantly, in Rails 3 and above, is ActiveRelations. This is where Queries are only executed when required. For example instead off User.all you could call User.scoped and when the iteration in the View occurs the SQL in executed. Powerful stuff and the Future of Rails.

Let us know how you get on... All the best.

Answer 2

You wrote:

but when I added them to my database via migrations it only took a few seconds to add them. For some reason I thought they would have to go through all of my entries (of which there are thousands) and index them.

Indexing wont take long unless you have millions of records. DB Indexing is just a sort, and recording that sort to be used later.

Your index applied to both new and existing records.

UPDATE

Biggest bang for your buck:

1. Move long running processes to delayed_job (or similar)
2. Get rid of n+1 queries

Memcache is nice, but complicates your app, and you typically don't get the boost until your app is db read bound.