Nobody has any clues? On Friday, June 28, 2013 3:43:56 PM UTC+2, Radosław Bułat wrote: > > 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.
