MySQL Forums
Forum List  »  Optimizer & Parser

Static (non correlated) sub-query instead of a join
Posted by: Dewey Gaedcke
Date: January 12, 2008 08:43AM

I have a small (un-indexed) memory/heap table (T3) that I use to filter a very large table (T2)......it's not good to join a large data set to any un-indexed table so I'm converting T3 to a literal instead. But When I run this query:

Explain extended
Select straight join T1.C1, T2.C1
from T1 Join T2 on
( T1.pk = T2.fk AND
Find_in_Set(T2.C1, (Select Group_Concat(T3.pk separator ',')
from T3 where T3.C2 = 'literal') )
); -- a few more tables down here T4, T5

Explain shows T3 as the LAST derived table even though I have tables T4 & T5 listed later. Since it's non-correlated (always returns a static scalar) I would expect the optimizer to constant fold it very early.

My T2 table is large and T3 is a tiny un-indexed temp table in memory and I'm trying to exclude T2 records from being selected before joining either to (non-indexed) T3 or to T4, T5, etc

We're running 5.1.22-rc-log and all suggestions appreciated.



Edited 1 time(s). Last edit at 01/12/2008 04:58PM by Dewey Gaedcke.

Options: ReplyQuote


Subject
Views
Written By
Posted
Static (non correlated) sub-query instead of a join
6070
January 12, 2008 08:43AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.