Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question

1 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.

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.