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.
