Rails - Nov 2007 - a few add_index questions..

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?


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Nov 13, 2007, at 9:49 PM, Josh 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?
>
> 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, foreign keys should be indexed.



--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Nov 13, 2007, at 7:49 PM, Josh 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?
>
> 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)



--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Nov 13, 2007, at 10:11 PM, Greg Willits wrote:
>
> On Nov 13, 2007, at 7:49 PM, Josh 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?
>>
>> 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)
You might want to read this:

http://www.slideshare.net/Blaine/scaling-twitter/

They have some interesting insights about indexing...

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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] ?

On Nov 14, 12:11 am, Greg Willits
<li...-0Bv1hcaDFPRk211Z5VL+QA@public.gmane.org>
wrote:
> On Nov 13, 2007, at 7:49 PM, Josh 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?
>
> > 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)

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

> 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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Nov 13, 2007 10:49 PM, Josh
<jjkiesch-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
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.
>
> 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.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---