On 10/14/05, Uwe Voelker <[EMAIL PROTECTED]> wrote: > SELECT DISTINCT > t1.ID AS t1_ID > FROM > MD t1 > LEFT OUTER JOIN MDV t2 ON(t1.ID = t2.MD) > WHERE > t2.MD IS NULL AND > ((t1.ID = t2.MD) OR (t2.MD IS NULL)) > > The last line is not neccessary. Does it come from the 'with_objects'? > But is it there really neccessary? I mean the outer join guaranties that > either t1.ID = t2.MD (the join condition) or t2.MD IS NULL (condition > not met, t2 filled with NULLs).
Don't you see this answer coming? :) It's MySQL being wacky again. If you don't include those redundant clauses, many (all?) versions of MySQL fail to use the appropriate indexes when running the query. You'll never see this effect when running "toy" examples, but I ran across it when adding joins to my benchmark suite. MySQL was taking *forever* to complete certain queries. I chased it down using EXPLAIN and then tried the old trick of adding redundant information to the WHERE clause. Sure enough, MySQL suddenly got the hint and started using its indexes. In RDBO, this tweak (like so many other weird things :) is only done when you're connected to a MySQL database. It's controlled by an (undocumented, for now) likes_redundant_join_conditions() method on the Rose::DB object, and can be overridden by the (also undocumented) redundant_join_conditions Manager param. All those undocumented things could be renamed or go away at any time, of course. If people really think they're necessary, I can make them "official" and document them. But I can't really see a reason to ever override them. -John ------------------------------------------------------- This SF.Net email is sponsored by: Power Architecture Resource Center: Free content, downloads, discussions, and more. http://solutions.newsforge.com/ibmarch.tmpl _______________________________________________ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
