(Note: I've asked this same question on stack overflow if you want some better syntax highlighting: http://stackoverflow.com/questions/5808863/how-can-i-better-optimize-many-to-many-association-queries-with-datamapper)
DataMapper appears to be generating grossly sub-optimal queries for associations that use a join table (whether I use :through => Resource, or an explicit join table). What can I do to improve the performance of these queries? Here's an example setup: class Left include DataMapper::Resource property :id, Serial has n, :joins has n, :rights, :through => :joins end class Join include DataMapper::Resource belongs_to :left, :key => true belongs_to :right, :key => true end class Right include DataMapper::Resource property :id, Serial property :one, String property :two, Integer end Now, say I have some data populated and I want to grab all the rights associated with a left object (let's just use the first one for ease): Left.first.rights DataMapper executes the following query (MySQL): SELECT rights.id, rights.one, rights.two FROM rights INNER JOIN joins ON rights.id = joins.right_id INNER JOIN lefts ON joins.left_id = lefts.id WHERE joins.left_id = 1 GROUP BY rights.id, rights.one, rights.two ORDER BY rights.id For one, the "INNER JOIN lefts ON joins.left_id = lefts.id" is completely unnecessary. I don't trust MySQL on this one, especially since the explain plan gives a "Using index; Using temporary; Using filesort". Also, what's up with the GROUP BY rather than SELECT DISTINCT? Another option is to explicity query through the join table via "Join.all(:left => Left.first).rights" or "Left.first.joins.rights". Both of these result in two queries: SELECT right_id FROM joins WHERE left_id = 1 SELECT id, one, two FROM rights WHERE id = 1 ORDER BY id I haven't profiled this exact setup, but in all cases where I'm following the same pattern, the first query is super slow (~100x slower than than the two query method to a localhosted mysql instance). -- You received this message because you are subscribed to the Google Groups "DataMapper" 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/datamapper?hl=en.
