Thanks, that makes sense. I thought it might be an actual mistake in the SQL (i.e. passing in the house.id into the subquery is forcing it to do a full table scan) rather than an optimization thing, but that makes more sense.
Yeah I knew going in that I would probably be spending some time hand tuning the queries once everything was working (kind of like making it right, then de-normalize where necessary). Though the framework I'm using is actually not too bad though, I can specify exact fields to add indexes with annotations right in the object and such.
In this case I don't even have to redo the query, I'll just manage the many to many relationship myself with a new domain object rather than letting the framework manage it itself.
|