Why add indexes to these columns in the Database?

Question

I am trying to get my head around this code. It's from the Rails Tutorial Book and is part of the process of making a twitter like application.

   class CreateRelationships < ActiveRecord::Migration
  def change
    create_table :relationships do |t|
      t.integer :follower_id
      t.integer :followed_id

      t.timestamps
    end

    add_index :relationships, :follower_id
    add_index :relationships, :followed_id
    add_index :relationships, [:follower_id, :followed_id], unique: true
  end
end

• Since their are only 2 columns (follower_id and followed_id) why would their be a need for an index?
• Does the index sort them is some way? It just seems a bit strange to me to add an index to a table with 2 columns?
• What does the index do to the rows?

Answer 1

Why would you add an index to the back of a book? So you can quickly look up a word and see which page numbers it's on.

Why is it fast to look up a word in a book index? Because the words in the index are sorted.

Similarly, in a database index, you can quickly look up a value and get the rows it is on.

1. The usage of indexes, and the number of columns, is not particularly related. You can have zero, one, or many indexes on (almost) any column in a table.

2. The index itself is a sorted structure, to allow for fast look-ups, but does not sort the physical rows of its table†.

3. An index doesn't do anything to the rows. It is a separate structure that points to rows. For example, if you're looking for follower_id 3, you could look at the index and it says, follower_id 3 is on rows 4, 9 and 99. This is faster than looking at every row for follower_id 3.

The last index in your example is a unique index, which prevents one from following the same person twice. An exception will be raised if you try to enter {follower_id=1, followed_id=2} and then {follower_id=1, followed_id=2}.

A unique index is similar to a primary key (though a primary key may not contain nulls), and is identical to a unique constraint in many systems††.

A final question to ask is, is it easy to be the guy that maintains the index of a book if the author is making a lot of revisions to the book?

† unless it's a clustered index

†† In Postgres a unique index may use expressions whereas a constraint may not