Hello. I've updated thinking sphinx gem from version 2.0.11 to 3.0.3 and indexing slowed down by factor > 5x. What I discovered is that ts:configure (previously ts:config) produces different sql selects for indexing. Version 3.0.3 add big GROUP BY clause to SELECT query. For example (it's simplified version of what I have):
ThinkingSphinx::Index.define :ad, :with => :active_record do indexes :id indexes website.url, :as => :website_url end Version 2.0.11 generates select_sql like: sql_query = SELECT SQL_NO_CACHE `ads`.`id` * CAST(8 AS SIGNED) + 0 AS `id` , `ads`.`id` AS `id`, `websites`.`url` AS `website_url`, `ads`.`id` AS `sphinx_internal_id`, 0 AS `sphinx_deleted`, 3798256122 AS `class_crc` FROM `ads` LEFT OUTER JOIN `websites` ON `websites`.`id` = `ads`.`website_id` WHERE (`ads`.`id` >= $start AND `ads`.`id` <= $end) GROUP BY `ads`.`id` ORDER BY NULL Version 3.0.3 generates: sql_query = SELECT SQL_NO_CACHE `ads`.`id` * 6 + 0 AS `id`, 'Ad' AS `sphinx_internal_class_name`, `ads`.`id` AS `id`, websites.`url` AS `website_url`, `ads`.`id` AS `sphinx_internal_id`, 'Ad' AS `sphinx_internal_class`, 0 AS `sphinx_deleted` FROM `ads` LEFT OUTER JOIN `websites` ON `websites`.`id` = `ads`.`website_id` WHERE (`ads`.`id` >= $start AND `ads`.`id` <= $end) GROUP BY `ads`.`id`, `ads`.`id`, websites.`url`, `ads`.`id` ORDER BY NULL Of course I have much more indexed columns and they are all present in GROUP BY clause which makes it very slow. When I removed GROUP BY clause and tested it in mysql console it was much more quicker. I have proper indexes on foreign keys. Is this behaviour expected? Why it's different comparing with previous version? What I can do with this to make it faster? -- You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/thinking-sphinx. For more options, visit https://groups.google.com/groups/opt_out.
