Performance can be gained on MySQL by using DISTINCT over GROUP BY in case of hasMany pre-query.
In my case, with 6 related tables this is from 1 min 25 sec -> 0.005 sec
When having a model with hasManys, and using the with function an initial query is fired. This looks like
FROM `table1` AS `Table1`
LEFT JOIN `relatedtableN` AS `aliasforN` ON [...] -- repeat for every relationship
GROUP BY Table1.key
I have a model with 6 hasMany relationships (and need them). Using a find first this select took running on a local MySQL* this took 1min25sec. Rewritten as a SELECT DISTINCT it was done in 0.005sec (MyISAM tables, InnoDb about same timings).
Having only 5 related tables will drop the time to about 2.02 sec in the GROUP BY flavour (DISTINCT still 0.005 sec)
Directly queried on a MySQL 5.5.16, Win7x64 8GB dual core, data tables are not that big (main table 7k rows, each related table about 20k rows), all the fields in the joins are indexed. Every query executed multiple times to get good readings.
It just so happens that all related tables in this case are all the same (the various hasMany have distinctive conditions),
Is this issue can be an additionnal reflexion topic for #530 ?
nb: the memory issue was caused by my logging statements (dumping the results)
This should have been solved in #705. The DISTINCT clause is now used in replacement of the GROUP BY clause for all "hasMany pre-queries".