On May 2, 2:47 pm, Clifford Heath <[email protected]> wrote: > On 03/05/2011, at 4:20 AM, Ian MacLeod wrote: > > > DataMapper appears to be generating grossly sub-optimal queries > > There's very little wrong with those queries, but there is a lot wrong > with the MySQL optimiser. It's a heuristic optimiser, and every other > RDBMS discarded that approach around 1985. Please consider using > an RDBMS with a proper optimiser. Postgres is a good free option.
I'd love to for future projects, but switching mid-stream isn't something I'd like to entertain. > > What can I do to improve the performance of these queries? > > You should index all foreign keys. That should get rid of the > temporary and sort in the query plan. Definitely, and DataMapper's dm-migrate extension generates indices for all FK's - so I'm covered with that. Nothing obvious is popping out for additional indices to index (unless that GROUP BY benefits from it - but I'd rather not have the whole table in an index...) > > For one, the "INNER JOIN lefts ON joins.left_id = lefts.id" is > > completely unnecessary. > > No, it isn't. You've asked DataMapper for the first Left, and all > its Rights, and that's exactly what it's giving you. It has no way > to know that you aren't using the Left it loads for you. Sure it does - DataMapper is generating the query without any references to the lefts table in the SELECT clause. I guess this guards against cases where the left row is missing but the join still exists - but the FK constraints wouldn't allow that, regardless. > Clifford Heath. -- 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.
