[thelist] an elegant correlated sub-query that doesn't want to

Sarah Adams sarahwbs at gmail.com
Thu Apr 16 14:24:44 CDT 2009


> And, Rudy, before you go knocking Oracle... the only reason there is a
> problem in the first place is that the data model has non-atomic
> values. I am a bit surprised that MySQL has built-in functions to
> support this.

I *think* Rudy was being facetious, since his column is (technically)
about Oracle :)

>> Now, do you suppose that MySQL will be smart enough not to do full-table
>> scans for this bit of the query? I'll still use it either way, I'm just
>> curious.
>>
>>   INNER
>>     JOIN ( SELECT o.OptionID
>>                 , CONCAT(s.NameDE,': ',o.NameDE) AS SetOption
>>              FROM OptionSets AS s
>>            INNER
>>              JOIN Options AS o
>>                ON o.SetID = s.SetID
>>            GROUP
>>                BY o.OptionID ) AS q
> 
> 
> Sarah, I'm not sure what part you are getting at. I don't see anything
> here that would trigger a full table scan.

I was thinking this might return a full set of all set/option combinations:

  SELECT o.OptionID
       , CONCAT(s.NameDE,': ',o.NameDE) AS SetOption
    FROM OptionSets AS s
  INNER
    JOIN Options AS o
      ON o.SetID = s.SetID
  GROUP
      BY o.OptionID

I'm hoping the optimizer can figure it out.

-- 
sarah adams
my son: http://clarkeadams.com
twitter: http://twitter.com/sarahwbs
portfolio: http://sarah.designshift.com



More information about the thelist mailing list