PudparK PudparK - 2 years ago 81
Ruby Question

Rails: Foreign Keys and when to index

Three questions:
When you create a model, is a foreign_key automatically created as well?

I'm thinking I should add_index when a column is unique to the table or unique in general, or when the column will relate to other databases, Amirite?

What will an index look like? Will it just use the contents of the cell?

enter image description here

Answer Source

1) Do you mean when using a generator? Generally speaking, you should generate migrations, rather than use a generator for the whole model/scaffolding. And then, no, a foreign key is not automatically created, only if you specify it.

2) add_index is going to come in handy for columns on big tables that need to be accessed quickly by your database. Let's say you've got a users table with an email column that must be unique, but isn't indexed. And your service grows, now you have millions of users, and you need to go User.find_by_email "someone@example.com". Without an index, that's going to take you a while. With an index, it'll be quick. That's when an index comes in handy.

3) Really depends on your database engine afaik. Not something that will affect your day-to-day imho (though if you have a specific database engine in mind, you can certainly find out). Here's the info on MySQL, straight from the source: https://dev.mysql.com/doc/refman/5.5/en/column-indexes.html

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download