# A few add\_index questions **URL:** \ **Category:** Rails\ **Created:** [November 14, 2007, 4:50am UTC](https://www.ruby-forum.com/t/a-few-add-index-questions/120147 "2007-11-14T04:50:32Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![jjkiesch](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/bc79bd/32.png) [@jjkiesch](https://www.ruby-forum.com/u/jjkiesch)\ **Post date:** [November 14, 2007, 4:50am UTC](https://www.ruby-forum.com/t/a-few-add-index-questions/120147/1 "2007-11-14T04:50:32Z")
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? ---
**Author:** ![jjkiesch](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/bc79bd/32.png) [@jjkiesch](https://www.ruby-forum.com/u/jjkiesch)\ **Post date:** [November 14, 2007, 6:52am UTC](https://www.ruby-forum.com/t/a-few-add-index-questions/120147/2 "2007-11-14T06:52:39Z")
On Nov 13, 2007, at 9:49 PM, Josh wrote: > adding too many keys slow the database down at all? > Yes, foreign keys should be indexed. ---
**Author:** ![jjkiesch](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/bc79bd/32.png) [@jjkiesch](https://www.ruby-forum.com/u/jjkiesch)\ **Post date:** [November 14, 2007, 7:11am UTC](https://www.ruby-forum.com/t/a-few-add-index-questions/120147/3 "2007-11-14T07:11:56Z")
On Nov 13, 2007, at 7:49 PM, Josh wrote: > 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? 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) ---
**Author:** ![jjkiesch](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/bc79bd/32.png) [@jjkiesch](https://www.ruby-forum.com/u/jjkiesch)\ **Post date:** [November 14, 2007, 7:24am UTC](https://www.ruby-forum.com/t/a-few-add-index-questions/120147/4 "2007-11-14T07:24:47Z")
On Nov 13, 2007, at 10:11 PM, Greg W. wrote: > > i realize this might not be the group to be asking about when and > > 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) You might want to read this: > **[Scaling Twitter](https://www.slideshare.net/Blaine/scaling-twitter)** > > Big Bird. (scaling twitter) Rails Scales. (but not out of the box) First, Some Facts • 600 requests per second. Growing fast. • 180 Rails Instances (Mongre... They have some interesting insights about indexing… ---
**Author:** ![jjkiesch](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/bc79bd/32.png) [@jjkiesch](https://www.ruby-forum.com/u/jjkiesch)\ **Post date:** [November 14, 2007, 9:16pm UTC](https://www.ruby-forum.com/t/a-few-add-index-questions/120147/5 "2007-11-14T21:16:35Z")
> 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 ---
**Author:** ![jjkiesch](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/bc79bd/32.png) [@jjkiesch](https://www.ruby-forum.com/u/jjkiesch)\ **Post date:** [November 14, 2007, 2:52pm UTC](https://www.ruby-forum.com/t/a-few-add-index-questions/120147/6 "2007-11-14T14:52:23Z")
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] ? ---
**Author:** ![jjkiesch](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/bc79bd/32.png) [@jjkiesch](https://www.ruby-forum.com/u/jjkiesch)\ **Post date:** [November 14, 2007, 9:49pm UTC](https://www.ruby-forum.com/t/a-few-add-index-questions/120147/7 "2007-11-14T21:49:58Z")
On Nov 13, 2007 10:49 PM, Josh [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > 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. > 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.