What's add_index used for in this code and more generally?

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 there are only 2 columns (follower_id and followed_id), why would their be a need for an index?
Does the index sort them in 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?
Is indexing optional? If so why/why not use it? Is it a good idea to use it in the code above?

Please answer all the questions if you can. I'm just trying to get my head around this concept and after reading about it I have these questions.

score 3 · Accepted Answer · edited May 23 '17 at 12:12

Since there are only 2 columns (follower_id and followed_id) why would there be a need for an index?

Need for indexing doesn't depend on the number of columns. It's used for speeding up the lookups. Even if your table has only one column, verifying whether a particular value is present in that column will need you to scan the whole table in the worst case. With an index it can be answered immediately.

Does the index sort them in some way? It just seems a bit strange to me to add an index to a table with 2 columns?

No, in general indexes don't sort the data in the table in any way. I say "in general" because clustered indices do sort the data. See this question for more details.

What does the index do to the rows?

Again, nothing in general. Different DBMSes use different mechanisms to associate a row in the table to the index. Indexing is one of the most important tasks in a DBA's work. It'd be great if you have basic ideas about it. Read the wikipedia article to get the basics.

Is indexing optional? If so why/why not use it?

Yes, indexing is optional. You should use indexes when you see your query performance go down. Again, different DBMSes provide different mechanisms for you to monitor your query performance and you should have monitoring in place to alert you when performance degrades beyond a threshold. With experience, you'll reach a point where most of the indexing needs of an application will be clear to you from the beginning.

Is it a good idea to use it in the code above?

Can't comment on that. Indexing needs of each application are different. You should be aware of downsides of over-indexing as well. If you have a lot of indexes, your updates, inserts and deletes will become slower with time since they will also need to update your indexes.

score 0 · Answer 2 · answered Nov 12 '13 at 08:12

An index on a column or set of columns speeds lookups on that column or set of columns. It's usefulness has nothing to do with the number of columns in the table since it's purpose is to locate the row(s) associated with the column values.

No, the index doesn't sort the table.

The index doesn't "do" anything to the rows, although if it's a "unique" index it would prevent the creation/update of rows which duplicate the column(s) in question.

Indexing is optional. It speeds up lookups, but takes additional time for write operations. Whether or not it is a good idea depends entirely on the application.

What's add_index used for in this code and more generally?

2 Answers2