Roy Lyseng wrote:
>
>
> Jay Pipes wrote:
>> Øystein Grøvlen wrote:
>>> Hi,
>>>
>>> Is sub-query materialization supposed to work in the context of BLOB
>>> columns? Given the following query (ref. Bug#48213) where column e
>>> is BLOB and the rest is VARCHAR:
>>>
>>> SELECT pk FROM t1 WHERE (b,c,d,e)
>>> IN (SELECT b,c,d,e FROM t2 WHERE pk > 0);
>>
>> I'm not sure about materialization, but the above can be transformed
>> into a standard inner join:
>>
>> SELECT t1.pk FROM t1
>> INNER JOIN t2
>> ON t1.a = t2.a
>> AND t1.b = t2.b
>> AND t1.c = t2.c
>> AND t1.d = t2.d
>> WHERE t2.pk > 0;
>>
>> If you can transform the query representation from the subquery to the
>> join, I believe the performance would be much better.
>
> Hi Jay!
>
> Generally, an INNER JOIN will produce a lot of unwanted duplicates that
> you need a DISTINCT (and hence a sort) to remove. So, efficient semijoin
> transformation or a materialization algorithm often perform much better
> than the join rewrite.
I see. Yes, without an index on b,c,d,e there might be benefits to the
materialization efforts. With an index on b,c,d,e though, the nested
loops join should be better...
Cheers!
Jay
