CODEWITHSUNDEEP
ruby-on-railsrubydatabasemodelassociations
inthenameofmusik
inthenameofmusik

Reputation: 623

Why Index is Needed for Rails Model Association

I'm a bit confused on the topic of index columns in model associations.

(From the The Rails 4 Way) We have User, Timesheet, and Expense Report models.

The User model:

has_many: timesheets
has_many: expense_reports

(along with the corresponding belongs_to in the other models)

The Rails 4 Way book says to add_index into the timesheets and expense_reports model as so:

add_index :timesheets, :user_id
add_index :expense_reports, :user_id

I don't understand the reasoning for adding an index after every foreign key column. The timesheet and expense_report tables already have a primary_key column so why isn't that used for "performance boos?" Adding two additional indices seems redundant to me? Can someone explain the benefit?

Upvotes: 2

Views: 1615

Answers (1)

Tim
Tim

Reputation: 1376

I can see where your frustration comes from, just follow along and I am sure you'll get it pretty fast...

Problem:

When you create a column in a database, it is vital to consider whether you will need to find and retrieve records from that column. So, lets say we have a User table and every user has an email, that in many applications is used for authentication and authorization of users. When we allow users to log in to our application, we will need to find the user record corresponding to the submitted email address. Unfortunately, the only way to find a user by email address is to scan through each user row in the database and compare its email attribute to the given email - which, therefore, implies we might have to examine every row(since the user can be the last person inside the database). This would take a lot of time as you can imagine. Simply, this is not good.

Solution:

Putting an index on the email column would fix the problem. Think of it as an appendix at the end of the book. In a book, to find all the occurrences of a given string, say “foobar”, you would have to scan each page for “foobar” - the paper version of a full-table scan. With a book index, on the other hand, you can just look up “foobar” in the index to see all the pages containing “foobar”. A database index works essentially the same way.

I hope this helps you out.

Upvotes: 2

Related Questions