[DataMapper] Re: joins include group by order by when not requested = causes SQL temporary sort table
Agreed, I would lve a way to globally disable this instead of having to monkey-patch :) -- You received this message because you are subscribed to the Google Groups DataMapper group. To post to this group, send email to datamap...@googlegroups.com. To unsubscribe from this group, send email to datamapper+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/datamapper?hl=en.
[DataMapper] Re: joins include group by order by when not requested = causes SQL temporary sort table
Kevin, Anyone else noticed this strangeness with GROUP BY and ORDER BY being included for all association retrievals? Any tips where to look in the source? That's just the default behaviour to make the RDBMS act more consistent with other datastores. The explicit grouping is to remove duplicate records from the results, which is consistent with relational algebra, where all results are sets (all records are unique). You can disable this behaviour by supplying two options to the finder: group.users(:unique = false, :order = nil) This should remove the GROUP BY as well as the ORDER BY. -- Dan -- You received this message because you are subscribed to the Google Groups DataMapper group. To post to this group, send email to datamap...@googlegroups.com. To unsubscribe from this group, send email to datamapper+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/datamapper?hl=en.
Re: [DataMapper] Re: joins include group by order by when not requested = causes SQL temporary sort table
On 15/07/2010, at 1:26 PM, Dan Kubb (dkubb) wrote: Anyone else noticed this strangeness with GROUP BY and ORDER BY being included for all association retrievals? That's just the default behaviour to make the RDBMS act more consistent with other datastores. The explicit grouping is to remove duplicate records from the results, which is consistent with relational algebra Dan, Did you investigate the performance cost of including Group By in cases where the results are already unique? The sorting that's required for ensuring uniqueness is often quite costly, forcing the optimiser to use temporary tables, and quite a few RDBMS optimisers don't detect the situation where the results must already be unique - so you incur an extra cost by including it. Clifford Heath, Data Constellation, http://dataconstellation.com Agile Information Management and Design. -- You received this message because you are subscribed to the Google Groups DataMapper group. To post to this group, send email to datamap...@googlegroups.com. To unsubscribe from this group, send email to datamapper+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/datamapper?hl=en.
[DataMapper] Re: joins include group by order by when not requested = causes SQL temporary sort table
Anyone else noticed this strangeness with GROUP BY and ORDER BY being included for all association retrievals? Any tips where to look in the source? -Kevin On Jun 27, 6:14 pm, Kevin inspir...@gmail.com wrote: Here's my next thing to hack/fix, hopefully: Groups HABTM Users. Calling group.users loads this SQL: ~ (0.000226) SELECT `id`, `title`, `group_apps` FROM `groups` WHERE `id` = 1 ORDER BY `id` LIMIT 1 ~ (0.000272) SELECT `users`.`id` FROM `users` INNER JOIN `groups_users` ON `users`.`id` = `groups_users`.`user_id` INNER JOIN `groups` ON `groups_users`.`group_id` = `groups`.`id` WHERE `groups_users`.`group_id` = 1 GROUP BY `users`.`id` ORDER BY `users`.`id` The Group by / Order by causes this in the explain for the query: Using index; Using temporary; Using filesort. Needless to say, with large result sets this won't work. Any tips? Is this a known problem anyone else has run into? Any suggestions where to start looking in the source? Ciao, Kevin -- You received this message because you are subscribed to the Google Groups DataMapper group. To post to this group, send email to datamap...@googlegroups.com. To unsubscribe from this group, send email to datamapper+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/datamapper?hl=en.
