Adding Indices in Rails for Efficiency

Flatiron School / 22 January 2013

The following is a guest post by Li Ouyang and originally appeared on her blog. Li is currently a student a The Flatiron School. You can learn more about her here, or follow her on twitter here.

Who doesn’t like to be fast? Especially for associations, adding an index will greatly improve your database queries. Think of your typical join table, ours is called mixtape_songs, which is filled with two foreign keys, joining a many-to-many relationship between a mixtape and a song. Adding an index to one or both of the foreign keys is similar to an index in a recipe book. If you wanted to look up all the chicken recipes, you would flip to the back, and find the pages that pertain to chicken. It would take you forever to start at page 1, searching for chicken recipes. 

To speed things up, create a migration, add an index to your join table.

In your migration file, make sure the line looks like this:

Before the index, this query ran in 2.6ms.

After the added index, it ran in 1.6ms, shaving off more than 1/3 of the run time!

What Is a N + 1 Selects Statement? Previous Post Next Post