# Seeing performance problems with has\_many :through relation **URL:** \ **Category:** Rails\ **Created:** [April 29, 2006, 7:39pm UTC](https://www.ruby-forum.com/t/seeing-performance-problems-with-has-many-through-relation/59169 "2006-04-29T19:39:54Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![aginghipster](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/e9c0ed/32.png) [@aginghipster](https://www.ruby-forum.com/u/aginghipster)\ **Post date:** [April 29, 2006, 7:39pm UTC](https://www.ruby-forum.com/t/seeing-performance-problems-with-has-many-through-relation/59169/1 "2006-04-29T19:39:54Z")
Greetings all, I am looking for some insight from some of the more experienced with has\_many :through relationships as I am having a hard time reconciling the performance I am seeing. I have 2 tables that are linked together via has\_many :through Table 1: urls ( there are about 7,000 records ) 2 fields, id and url Table 2: phrases ( there are about 87,000 records ) 2 fields, id and phrase (w/ an index defined on phrase) A given url can be associated w/ many phrases and a given phrase can be associated with many urls. Table 3: relations ( there are about 235,000 records ) id, url\_id, phrase\_id and isrel (int) w/ an index created on url\_id,phrase\_id In Relation model I have belongs\_to :url belongs\_to :phrase In Url model I have has\_many :relations has\_many :phrases, :through =\> :relations In Phrase model I have has\_many :relations has\_many :urls, :through =\> :relations For a given phrase I can access phrase.urls wthout any performance issues, I see this in my Lighttpd console: Url Load (0.423328) SELECT urls.\* FROM urls INNER JOIN relations ON urls.id = relations.url\_id WHERE (relations.phrase\_id = 12) The problem is apparent when I try to access url.phrases for any url object, my console screen fills up with these: Phrase Load (0.006682) SELECT phrases.\* FROM phrases INNER JOIN relations ON phrases.id = relations.phrase\_id WHERE (relations.url\_id = 987) one of those messages for each url, and there can be up to 7k urls for a given scan. I know little about optimizing MySQL databases and I am wondering if I need to tweak the way the tables are structured or if its something I can fine-tune in rails. If it helps the indices were defined on the relations table as follows: CREATE unique index phrase\_url on relations (phrase\_id, url\_id) CREATE unique index url\_phrase on relations (url\_id,phrase\_id) ( started with just the first index and added the second when these issues started popping up ) I am hoping someone can spot the problem and suggest a modification to either the DB or the relationship in rails that leads to a solution. Thanks for any suggestions! Andy ---
**Author:** ![aginghipster](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/e9c0ed/32.png) [@aginghipster](https://www.ruby-forum.com/u/aginghipster)\ **Post date:** [April 30, 2006, 7:39pm UTC](https://www.ruby-forum.com/t/seeing-performance-problems-with-has-many-through-relation/59169/2 "2006-04-30T19:39:18Z")
Anyne have any ideas what may be wrong ( am hoping my post just slipped under the radar of someone who may be able to help )… Greetings all, I am looking for some insight from some of the more experienced with has\_many :through relationships as I am having a hard time reconciling the performance I am seeing. I have 2 tables that are linked together via has\_many :through Table 1: urls ( there are about 7,000 records ) 2 fields, id and url Table 2: phrases ( there are about 87,000 records ) 2 fields, id and phrase (w/ an index defined on phrase) A given url can be associated w/ many phrases and a given phrase can be associated with many urls. Table 3: relations ( there are about 235,000 records ) id, url\_id, phrase\_id and isrel (int) w/ an index created on url\_id,phrase\_id In Relation model I have belongs\_to :url belongs\_to :phrase In Url model I have has\_many :relations has\_many :phrases, :through =\> :relations In Phrase model I have has\_many :relations has\_many :urls, :through =\> :relations For a given phrase I can access phrase.urls wthout any performance issues, I see this in my Lighttpd console: Url Load (0.423328) SELECT urls.\* FROM urls INNER JOIN relations ON urls.id = relations.url\_id WHERE (relations.phrase\_id = 12) The problem is apparent when I try to access url.phrases for any url object, my console screen fills up with these: Phrase Load (0.006682) SELECT phrases.\* FROM phrases INNER JOIN relations ON phrases.id = relations.phrase\_id WHERE (relations.url\_id = 987) one of those messages for each url, and there can be up to 7k urls for a given scan. I know little about optimizing MySQL databases and I am wondering if I need to tweak the way the tables are structured or if its something I can fine-tune in rails. If it helps the indices were defined on the relations table as follows: CREATE unique index phrase\_url on relations (phrase\_id, url\_id) CREATE unique index url\_phrase on relations (url\_id,phrase\_id) ( started with just the first index and added the second when these issues started popping up ) I am hoping someone can spot the problem and suggest a modification to either the DB or the relationship in rails that leads to a solution. Thanks for any suggestions! Andy ---
**Author:** ![aginghipster](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/e9c0ed/32.png) [@aginghipster](https://www.ruby-forum.com/u/aginghipster)\ **Post date:** [May 3, 2006, 6:56pm UTC](https://www.ruby-forum.com/t/seeing-performance-problems-with-has-many-through-relation/59169/3 "2006-05-03T18:56:53Z")
Andrew, So it sounds like you’re doing a find to pull out a big list of urls, and then calling url.phrases on each one? If this is the case, you should be able to add ‘:include =\> :phrases’ to that initial find to make it all happen in one query. Pete Y. [http://9cays.com](http://9cays.com)