Rake Routes

by Stephen Ball

Increase Rails Performance with Database Indexes

Ever added a belongs_to or has_many to a model in Rails? Did you remember to add the database index? Find out why you should and how it’s done in this guest article by yodatravis.

If you are looking to increase the performance of your data driven web application, the answer may be as simple as adding an i    ndex to the correct column of your database table.

A database index is exactly what it sounds like. If you think of the index at the back of a reference book: a quickly searchable list of pointers to get to the actual data. Without an index, a database query might have to look at every row in your database table to find the correct result.

If you find that a SQL query is causing a performance problem and it contains a where clause or a join then adding an index will greatly speed it up.

A basic example: searching a table for data

Let’s say you are routinely querying your users table.

User.where(:email => current_user.email)

Rails automatically adds an index to the id field of a database table, but in this case we’re asking it to find a row based on another piece of data. If you do that often enough you should almost certainly add an index for that data.

# a migration to index to the users email column
class AddEmailIndexToUsers < ActiveRecord::Migration
  def change
    add_index :users, :email, :unique => true

Joining tables

Another classic example of needing to add an index to increase performance is when you are joining database tables on two unindexed strings. For instance: all users joined by email address to a table of invitations.

class User < ActiveRecord::Base
  has_many :order_invites, :foreign_key => 'email', :primary_key => 'email'

If either the Users or Invitations table has a large number of rows this query will not perform well because the database is having to scan every record in the invitations table to check for a match against the email from the users table.

This is easily fixed by adding an index to the email column on the invitations table.

class AddEmailIndexToInvitations < ActiveRecord::Migration
  def change
    add_index :invitations, :email

Now, instead of having to go through every row in the invitations table, the database can just look up the email in the index and go right to the row we care about.

How indexes actually work

When a database is told to keep an index on a column, an ordered list (a binary tree technically) is created that gives the database a faster way to search for certain values in that column. If you were told to look through a shuffled deck of cards to find all the Jacks you would have to look at each card until you find them (close to 52 card views). However if that deck was indexed by card value (Ace, 2, 3, … King) then you could quickly move to where the Jacks should be and find all four of them (let’s say 4 or 5 card views). This performance gain is significantly magnified when your table has large numbers of rows.

When not to use indexes

By now, you might be thinking: why not just add an index to every database table column? Don’t do that.

There is a performance hit with indexes. Although select queries can be significantly faster, inserts and updates are marginally slower because there is overhead in maintaining the index. However the small impact (milliseconds) during an insert is usually a small price to pay for what could be seconds (or even minutes) saved on certain queries.

Rails 3.2 helps to identify where you need indexes

Rails 3.2 introduces the automated explain plan.  By default, queries that take longer than 0.5 secs will trigger an explain on the query. The rails development log will automatically contain a warning and you will see a database explain plan in the output.

The next time this happens to you, stop and think, Maybe I need an index.”

Up next Parsing Dates and Times from Strings using strptime Howdy ya’ll. As much as we’d prefer to just deal with nicely formatted data; the real world sometimes requires that we parse weird datetime strings Let’s Write a Gem: Part 1 Gems. Gotta love em. If you’re a Ruby developer then you already know that gems are simply fundamental to Ruby programming. Let’s write one. Right
Latest posts Where did the recent Elixir posts go? A subtle Go bug that types cannot help with swapcase with the tr command nice go test output See where vim settings came from Containers in the real world and backpressure in distributed systems Elixir Phoenix and “role postgres does not exist” From awk to a Dockerized Ruby Script Finding leap years with the cal command The Problem of State Clojure Functions in Four Ways See Some Clojure A simple language spec isn’t a feature when you’re building applications The Fastest Possible Tests Shrink your data into bitfields (and out again) Every “if” statement is an object waiting to be extracted Choose Generic Tools Hyperlinks you might find interesting — #4 Running bundle install on rails master Use tldr for command line examples Friday Lunch Links — #3 Friday Lunch Links — #2 Logical Solver: Turn facts into conclusions Programming with jq Command line tools - jq Friday Lunch Links — #1 Why diversity matters Music for coding - October 2019 Code puzzles are a poor way to gauge technical candidates Add vim to a pipeline with vipe Connecting Objects with Observable