John Siracusa wrote:
>On 12/5/06 9:50 AM, Kurt Hansen wrote:
>
>
>>I must be missing something. When Manager creates a JOIN, it adds a "OR
>>(tN.FK IS NULL)" to the where clause even though FK is defined as
>>not_null => 1 in tN. [...]
>>
>>Here are the offending FROM and WHERE clauses:
>>
>>FROM
>> transactions t1
>> LEFT OUTER JOIN persons t2 ON(t1.id = t2.transaction_id)
>> LEFT OUTER JOIN customs t3 ON(t1.id = t3.transaction_id)
>> LEFT OUTER JOIN donorccs t4 ON(t1.id = t4.transaction_id)
>> LEFT OUTER JOIN baskets t5 ON(t1.id = t5.transaction_id)
>>WHERE
>> t1.Result = 0 AND
>> NOT(t5.Sustainer = 'Yes') AND
>> ((t1.id = t2.transaction_id) OR (t2.transaction_id IS NULL)) AND
>> ((t1.id = t3.transaction_id) OR (t3.transaction_id IS NULL)) AND
>> ((t1.id = t4.transaction_id) OR (t4.transaction_id IS NULL)) AND
>> ((t1.id = t5.transaction_id) OR (t5.transaction_id IS NULL))
>>
>>
>
>Do you really want a LEFT OUTER JOIN, or is a simple join sufficient? In
>other words, are you trying to get only the "t1"s that have corresponding
>"t2"s, "t3"s, "t4"s, and "t5"s. If so, use require_objects instead of
>with_objects in your Manager query.
>
>If you do want "t1"s even if they don't have one or more related rows in
>t[2-5]s, then using with_objects is correct. The redundant JOIN conditions
>are added for MySQL only. They're there to persuade MySQL to use its
>indexes. (In some cases, MySQL was ignoring relevant indexes when those
>clauses were omitted.) The "IS NULL" parts have to be there in order to
>preserve the semantics of the query (i.e., we still want t1 rows even if one
>or more corresponding t[2-5] rows do not exist).
>
>It's not that the actual FK columns in the database will ever be null. It's
>that such a row may not exist at all for a given t1 row, but we still want
>to include that t1 row in the query, in which case all the result rows for
>that other tN table will be null.
>
>
Thank you, John. That explanation really helped. It cleared a lot of
cobwebs from my understanding of what is going on. After reading this, I
better understood both what QueryBuilder is doing and how to optimize my
database. Adding an index to one of the tables plus adding an additional
index to another table gave me a comparable performance boost.
I'm also planning on looking into the with_objects vs. require_objects
for further optimization.
>Anyway, to disable this feature, add this parameter to the Manager call:
>
> redundant_join_conditions => 0
>
>
Thanks!
>Is anyone else running into performance problems with this? If so, let me
>know and I'll change the default to be off instead of on for MySQL. If
>you'd like to do this yourself right now, you can override the
>likes_redundant_join_conditions() method in the Rose::DB driver class:
>
> use Rose::DB::MySQL;
> no warnings 'redefine';
> sub Rose::DB::MySQL::likes_redundant_join_conditions { 0 }
>
>
Since adding an index to a table fixed most of my problem, I'd suggest
keeping it as it is. However, it would be great if the above explanation
could be added to the documentation of Manager or QueryBuilder or to an
FAQ, e.g. "What to look for if your MySQL query is awwwwfully slow." :-)
Take care,
Kurt
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys - and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
