a few add_index questions..

rubyonrails-talk
1

on previous apps i hadn't really worried much about adding foreign key indexes on my tables just because they've been fairly small and i didn't really think i needed them.

well, i'm working on something now that has the potential to have a lot more data, and a lot more foreign keys.

so i have 3 tables... users, projects, and tasks.. should i be using add_index on every foreign key in all of the tables?

in my tasks table, i'm going to have a foreign key for the user and the project. seems like i could add up to 3 indexes..

add_index :tasks, [:project_id] add_index :tasks, [:user_id] add_index :tasks, [:project_id, :user_id], :unique => true

i realize this might not be the group to be asking about when and where to add indexes and keys to a database, but in the context of ruby and rails i just thought i would see what other people do. would adding too many keys slow the database down at all?

2

Yes, foreign keys should be indexed.

3

Yes your foreign keys should be indexed, and sometimes even other fields should be indexed as well if they're used to frequently find records.

Technically adding indexes makes inserting and deleting take a squeek longer because there's an extra task to do to maintain the index, but we're talking about negligible difference to most apps, and the benefit of the index far outweighs the cost. What you don't want to do is index every field involved in searches. MySQL only uses one index per search, so having extra indexes does become a useless burden.

-- gw (www.railsdev.ws)

4

You might want to read this:

They have some interesting insights about indexing...

5

when searching a table and using multiple columns, is that why you would put an index on multiple columns? so if i'm searching the tasks table by user_id and by project_id i would put add_index :tasks, [:project_id, :user_id] ?

6

when searching a table and using multiple columns, is that why you would put an index on multiple columns? so if i'm searching the tasks table by user_id and by project_id i would put add_index :tasks, [:project_id, :user_id] ?

Yes, or if you were often selecting by project_id and ordering by user_id.

- donald

7

on previous apps i hadn't really worried much about adding foreign key indexes on my tables just because they've been fairly small and i didn't really think i needed them.

well, i'm working on something now that has the potential to have a lot more data, and a lot more foreign keys.

so i have 3 tables... users, projects, and tasks.. should i be using add_index on every foreign key in all of the tables?

Depends on how many rows and how selective the index is.

in my tasks table, i'm going to have a foreign key for the user and the project. seems like i could add up to 3 indexes..

add_index :tasks, [:project_id] add_index :tasks, [:user_id] add_index :tasks, [:project_id, :user_id], :unique => true

i realize this might not be the group to be asking about when and where to add indexes and keys to a database, but in the context of ruby and rails i just thought i would see what other people do. would adding too many keys slow the database down at all?

It's a good question. Indexes work best with columns that have many different values and that will be queried by those values (so foreign key columns are a natural). The trade off is that indexes slow down inserts and updates to the indexed columns, because the index has to be adjusted to reflect the new keys.

Note that if you have an index on project_id+user_id, you don't need a separate index on project_id. The first index can be used to optimize searches that contain only a project_id. This applies only to the *leading* column(s) of a multi-column index, so you would need a separate index on user_id.