It would probably run a lot faster if you had a view and separate indexes on the two uid columns. I haven't got access to my MySQL at the moment, so i can't check the syntax for creating a view but you should be able to find that yourself. Something like:
CREATE VIEW friendshipview AS (<the union select statement>) Creating separate indexes on the columns used in the select statements should speed things up quite a bit, an index should speed up by a factor of 10^2 or 3. If you have a very active database you might also consider rebuilding the index regularly, with OPTIMIZE TABLE <table name>. -----Oprindelig meddelelse----- Fra: [email protected] [mailto:[email protected]] På vegne af Fresh Mix Sendt: 22. december 2011 21:33 Til: [email protected] Emne: [Rails] Re: SV: Simple friendship table Brynjolfur Thorvardsson wrote in post #1037658: > (SELECT uid2 AS uid, status FROM friendships WHERE uid1 = 1) UNION > (SELECT uid1,status FROM friendships WHERE uid2 = 1) It works fine, but it is slow :( Only one entry per friendship (1 500 000 rows in database) SELECT with UNION: 1.2108 sek Dublicate entryes ( total 3 000 000 rows in database) SELECT without UNION: 0.0007 sek -- 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 [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en. -- 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 [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
