Hi Clemens Perhaps this change would be possible - it would need some reflection upon the join, checking the following:
* is it a HABTM? * Are you requesting the primary key? * Is that used for the join against the mapping table? If so, then yes, the single join makes sense, and tweak the attribute and SQL accordingly from within TS. Not the simplest of patches, though... feel free to give it a shot, as I've no idea when I'll get to this. Cheers -- Pat On 09/06/2011, at 7:00 PM, Clemens Kofler wrote: > Hi there, > > when debugging, we found that there might be an unnecessary join for > has_and_belongs_to_many relationships. Here's the part of the index that's > relevant: > > class Product < ActiveRecord::Base > belongs_to :merchant > > define_index do > has merchant.countries(:id), :as => :country_ids, :type => :multi > end > end > > class Merchant < ActiveRecord::Base > has_and_belongs_to_many :countries > end > > In words: A product belongs to a merchant and each merchant delivers to a set > of countries. > > This creates an SQL query for sphinx that includes: > > LEFT OUTER JOIN `countries_merchants` ON `countries_merchants`.`merchant_id` > = `merchants`.`id` > LEFT OUTER JOIN `countries` ON `countries`.`id` = > `countries_merchants`.`country_id` > > and > > GROUP_CONCAT(DISTINCT IFNULL(`countries`.`id`, '0') SEPARATOR ',') AS > `country_ids` > > So far as I can see (and manual testing confirms this), this would do very > much the same: > LEFT OUTER JOIN `countries_merchants` ON `countries_merchants`.`merchant_id` > = `merchants`.`id` > GROUP_CONCAT(DISTINCT IFNULL(`countries_merchants`.`country_id`, '0') > SEPARATOR ',') AS `country_ids` > > From what our manual testing tells us, this is significantly faster for > products belonging to merchants that deliver to many countries, so it would > definitely pay off for us. > > Question is: Can I somehow force TS to generate a query like this without > having to manage the config manually? > > Thanks a bunch, > - Clemens > > -- > You received this message because you are subscribed to the Google Groups > "Thinking Sphinx" 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/thinking-sphinx?hl=en. > -- You received this message because you are subscribed to the Google Groups "Thinking Sphinx" 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/thinking-sphinx?hl=en.
