Hello Pat, Tom,
Thanks for all your help. i finally got it working as Pat described
although the resultant SQL was prohibitively slow but I've managed to
fix this by modifying the query in the resultant sphinx.conf file.
This is the class
class Model < ActiveRecord::Base
has_many :taggings, :dependent => :destroy
has_many :tags, :through => :taggings
has_many :expert_tags, :through => :taggings, :conditions =>
{'taggings.standard' => 3}, :source => :tag
has_many :intermediate_tags, :through => :taggings, :conditions =>
{'taggings.standard' => 2}, :source => :tag
has_many :basic_tags, :through => :taggings, :conditions =>
{'taggings.standard' => 1}, :source => :tag
define_index do
indexes [first_name, surname], :as => :full_name
indexes gender
indexes expert_tags(:name), :as => :expert_skills
indexes intermediate_tags(:name), :as => :intermediate_skills
indexes basic_tags(:name), :as => :basic_skills
set_property :field_weights => {
"expert_skills" => 30,
"intermediate_skills" => 10,
"basic_skills" => 1
}
end
end
This is the original SQL
SELECT `models`.`id` * 1 + 0 AS `id` ,
CAST(CONCAT_WS(' ', `models`.`first_name`, `models`.`surname`) AS
CHAR) AS `full_name`,
CAST(`models`.`gender` AS CHAR) AS `gender`, CAST(GROUP_CONCAT
(`tags`.`name` SEPARATOR ' ') AS CHAR) AS `expert_skills`,
CAST(GROUP_CONCAT(`intermediate_tags_models`.`name` SEPARATOR ' ') AS
CHAR) AS `intermediate_skills`,
CAST(GROUP_CONCAT(`basic_tags_models`.`name` SEPARATOR ' ') AS CHAR)
AS `basic_skills`,
`models`.`id` AS `sphinx_internal_id`,
374627805 AS `class_crc`,
'374627805' AS `subclass_crcs`,
0 AS `sphinx_deleted` FROM models
LEFT OUTER JOIN `taggings` ON (`models`.`id` = `taggings`.`model_id`)
LEFT OUTER JOIN `tags` ON (`tags`.`id` = `taggings`.`tag_id`) AND
`taggings`.`standard` = 3
LEFT OUTER JOIN `taggings` intermediate_tags_models_join ON
(`models`.`id` = `intermediate_tags_models_join`.`model_id`)
LEFT OUTER JOIN `tags` intermediate_tags_models ON
(`intermediate_tags_models`.`id` =
`intermediate_tags_models_join`.`tag_id`) AND `taggings`.`standard` =
2
LEFT OUTER JOIN `taggings` basic_tags_models_join ON (`models`.`id` =
`basic_tags_models_join`.`model_id`)
LEFT OUTER JOIN `tags` basic_tags_models ON (`basic_tags_models`.`id`
= `basic_tags_models_join`.`tag_id`) AND `taggings`.`standard` = 1
WHERE `models`.`id` >= $start AND `models`.`id` <= $end
GROUP BY `models`.`id` ORDER BY NULL
This is my modified SQL:
SELECT `models`.`id` * 1 + 0 AS `id` ,
CAST(CONCAT_WS(' ', `models`.`first_name`, `models`.`surname`) AS
CHAR) AS `full_name`,
CAST(`models`.`gender` AS CHAR) AS `gender`,
CAST(GROUP_CONCAT(`expert_tags`.`name` SEPARATOR ' ') AS CHAR) AS
`expert_skills`,
CAST(GROUP_CONCAT(`intermediate_tags`.`name` SEPARATOR ' ') AS CHAR)
AS `intermediate_skills`,
CAST(GROUP_CONCAT(`basic_tags`.`name` SEPARATOR ' ') AS CHAR) AS
`basic_skills`,
`models`.`id` AS `sphinx_internal_id`,
374627805 AS `class_crc`,
'374627805' AS `subclass_crcs`,
0 AS `sphinx_deleted` FROM models
LEFT OUTER JOIN `taggings` ON (`models`.`id` = `taggings`.`model_id`)
LEFT OUTER JOIN `tags` expert_tags ON (`expert_tags`.`id` =
`taggings`.`tag_id`) AND `taggings`.`standard` = 3
LEFT OUTER JOIN `tags` intermediate_tags ON (`intermediate_tags`.`id`
= `taggings`.`tag_id`) AND `taggings`.`standard` = 2
LEFT OUTER JOIN `tags` basic_tags ON (`basic_tags`.`id` =
`taggings`.`tag_id`) AND `taggings`.`standard` = 1
WHERE `models`.`id` >= 1 AND `models`.`id` <= 3000
GROUP BY `models`.`id` ORDER BY NULL
This really seemed to speed things up. The original SQL didn't
complete even after 40 minutes. Then new SQL completed in about 2
minutes.
Thanks for all your help. It's been a mission to get this working
nicely. Thank you for such an excellent plugin.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
