Hi, I don''t quite understand the syntax for database indexes and/or how the work to help speed up specific database searches. Can somebody suggest a set of indexs that would make this set of queries go faster? Contact Load (0.001406) SELECT * FROM `contacts` WHERE (`contacts`.user_id = 1) Phone Load (0.003268) SELECT * FROM `phones` WHERE (`phones`.contact_id = 8485) Email Load (0.000371) SELECT * FROM `emails` WHERE (`emails`.contact_id = 8485) Note Load (0.019782) SELECT * FROM `notes` WHERE (`notes`.contact_id = 8485) Group Load (0.000154) SELECT `groups`.* FROM `groups` INNER JOIN groupers ON groups.id = groupers.group_id WHERE ((`groupers`.contact_id = 8486)) a Contact has_many phones, emails, notes. a Contact has_many groups :through groupers Thanks much, and thanks even more if you include some explanation! thanks, jp -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 Dec 20, 1:01 am, Jeff Pritchard <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> Hi, > I don''t quite understand the syntax for database indexes and/or how the > work to help speed up specific database searches. Can somebody suggest > a set of indexs that would make this set of queries go faster?[...] Very simple: you can profit by indexing on almost anything you''re going to be doing a lot of searching on. In this case, that means that you should consider building indices on your foreign key fields (such as contacts.user_id), since they''re what you''re searching on in these queries. But before you do that, you might want to try EXPLAIN SELECT on the queries you''re interested in, and see what mySQL tells you about where the bottlenecks are. Best, -- Marnen Laibow-Koser marnen-iDrGZlD6aOYdnm+yROfE0A@public.gmane.org http://www.marnen.org --~--~---------~--~----~------------~-------~--~----~ 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 20 Dec 2008, at 07:11, Marnen Laibow-Koser wrote:> > > On Dec 20, 1:01 am, Jeff Pritchard <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> > wrote: >> Hi, >> I don''t quite understand the syntax for database indexes and/or how >> the >> work to help speed up specific database searches. Can somebody >> suggest >> a set of indexs that would make this set of queries go faster? > [...] > > Very simple: you can profit by indexing on almost anything you''re > going to be doing a lot of searching on. In this case, that means > that you should consider building indices on your foreign key fields > (such as contacts.user_id), since they''re what you''re searching on in > these queries. But before you do that, you might want to try EXPLAIN > SELECT on the queries you''re interested in, and see what mySQL tells > you about where the bottlenecks are. >All of the above is good. To explain a little more if you do SELECT * from contacts where contacts.user_id = 2356 and there is no index then the database must examine every single row in the contacts table to see if it has the right user_id whereas if it has an index it can go straight there. (similar to how if you have a phone book and you''re looking for all entries ''Michael smith'' you don''t have to read the entire phone book, you can jump to the appropriate page very quickly) Fred> Best, > -- > Marnen Laibow-Koser > marnen-iDrGZlD6aOYdnm+yROfE0A@public.gmane.org > http://www.marnen.org > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Frederick Cheung wrote:> On 20 Dec 2008, at 07:11, Marnen Laibow-Koser wrote: > >> [...] >> >> Very simple: you can profit by indexing on almost anything you''re > All of the above is good. To explain a little more if you do > > SELECT * from contacts where contacts.user_id = 2356 > > and there is no index then the database must examine every single row > in the contacts table to see if it has the right user_id whereas if it > has an index it can go straight there. (similar to how if you have a > > FredThanks Marnen and Fred, Does the same logic completely cover joins, or is there something special to consider for those? thanks, jp -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 20 Dec 2008, at 16:45, Jeff Pritchard wrote:> > Frederick Cheung wrote: >> On 20 Dec 2008, at 07:11, Marnen Laibow-Koser wrote: >> >>> [...] >>> >>> Very simple: you can profit by indexing on almost anything you''re >> All of the above is good. To explain a little more if you do >> >> SELECT * from contacts where contacts.user_id = 2356 >> >> and there is no index then the database must examine every single row >> in the contacts table to see if it has the right user_id whereas if >> it >> has an index it can go straight there. (similar to how if you have a >> >> Fred > > Thanks Marnen and Fred, > Does the same logic completely cover joinsYup Fred> , or is there something > special to consider for those? > > thanks, > jp > -- > Posted via http://www.ruby-forum.com/. > > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---