Hi Radosław There was something in older versions of Thinking Sphinx that checked whether MySQL was being used, and then checked if that MySQL server had a particular flag set (to see whether GROUP BY clauses must be complete, or could be missing/incomplete). If the latter was true, then the GROUP BY clause was not included.
I'm not particularly keen with having the special behaviour for one database, but I understand it's not ideal from a speed perspective for MySQL users. I'd be open to a patch that adding this behaviour in, but I'd much prefer a solution that offered appropriate hooks for other devs to extend the SQL generation themselves directly. I've no idea what that would look like, though, so even just the simpler patch would be a good start. Kind regards, -- Pat On 01/07/2013, at 7:02 AM, Radosław Bułat wrote: > 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. > > -- 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.
