List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:August 30 1999 10:18pm
Subject:Re: EXISTS, NOT EXISTS
View as plain text  
Long posting ahead ... be warned :-)

On Mon, 1999-08-30 14:25:29 +0300, Jussi Jääskeläinen wrote:
> Problem is that (according documentations) MySql don't support
> SQL-commands "EXISTS" and "NOT EXISTS" what our code is full-off...
> 1) Any plans to include those commands to MySql-server
> 2) Posibility to go around problem with some SQL-code modifications...

I'll give (2) a try ...

Your query:
>       'SELECT * FROM SpotBlocks97 ulkosp WHERE EXISTS ('
>       ' SELECT * FROM SpotBlocks97 sp, RADIOID97 rd, SUBGROUPS97 sb'
>       ' WHERE'
>       '      sp.active=1 AND'
>       '      rd.id=:BoxID AND'
>       '      (sp.enddate is null or (sp.enddate is not null AND
> sp.enddate>=:Curdate)) AND'
>       '      (sp._group is null OR'
>       '      (sp._group=rd._group AND'
>       '      (sp.subgroup is null or (sp.subgroup IN'
>       '      (SELECT DISTINCT subgroup from boxsubgroups97 where
> Maingroup=rd._group and ID=rd.id)'
>       '     ))))'
>       '  AND sp.Number=ulkosp.Number'
>       ')'
>       'ORDER BY 1'

* Reformat this beast for better readability (at least in my eyes)
  --------

  SELECT * FROM SpotBlocks97 AS ulkosp WHERE EXISTS
  (
   SELECT *
   FROM SpotBlocks97 sp,
        RADIOID97    rd,
        SUBGROUPS97  sb
   WHERE
        sp.active=1
    AND rd.id=:BoxID
    AND (   sp.enddate IS NULL
         OR (    sp.enddate IS NOT NULL
             AND sp.enddate>=:CurDate
            )
        )
    AND (   sp._group  IS NULL
         OR (    sp._group=rd._group
             AND (   sp.subgroup IS NULL
                  OR (sp.subgroup IN (SELECT DISTINCT subgroup
                                      FROM boxsubgroups97
                                      WHERE Maingroup=rd._group
                                        AND ID=rd.id
                                     )
                     )
                 )
            )
        )
    AND sp.Number=ulkosp.Number
  )
  ORDER BY 1;

* Simpliyfing, step 1
  -------------------
  The base structure of the query is:

  SELECT * FROM SpotBlocks97 AS ulkosp WHERE EXISTS
  (
   SELECT *
   FROM SpotBlocks97 sp, ...
   WHERE ...
    AND sp.Number=ulkosp.Number
  )

  This can be rewritten without EXISTS and a sub-query by doing
  another join:

  SELECT DISTINCT ulkosp.*
  FROM SpotBlocks97 AS ulkosp, SpotBlocks97 AS sp, ...
  WHERE ...
   AND sp.Number=ulkosp.Number

* Simpliyfing, step 2
  -------------------
  The other sub-query is:

     sp.subgroup IN (SELECT DISTINCT subgroup
                     FROM boxsubgroups97
                     WHERE Maingroup=rd._group
                       AND ID=rd.id
                    )

  This can be rewritten using another join

     FROM ..., boxsubgroups97 AS sg
     WHERE ...
      AND sp.subgroup = sg.subgroup
      AND rd._group   = sg.Maingroup
      AND rd.id       = sg.ID       

  This rewrite might give several identical result rows for
  each of the matching rows in boxsubgroups97, but as we're using
  SELECT DISTINCT this doesn't matter.

* Simpifying, step 3
  ------------------
  The term

     (   sp.enddate IS NULL
      OR (    sp.enddate IS NOT NULL
          AND sp.enddate>=:CurDate
         )
     )

  can be simplified to

     (   sp.enddate IS NULL
      OR sp.enddate>=:CurDate
     )

* All steps applied
  -----------------

  SELECT DISTINCT ulkosp.*
  FROM SpotBlocks97   AS ulkosp,
       SpotBlocks97   AS sp,
       RADIOID97      AS rd,
       SUBGROUPS97    AS sb,
       boxsubgroups97 AS sg
  WHERE
       ulkosp.Number=sp.Number
   AND sp.active=1
   AND rd.id=:BoxID
   AND (   sp.enddate IS NULL
        OR sp.enddate>=:CurDate
       )
   AND (   sp._group  IS NULL
        OR (    sp._group=rd._group
            AND (   sp.subgroup IS NULL
                 OR (     sp.subgroup = sg.subgroup 
                      AND rd._group   = sg.Maingroup
                      AND rd.id       = sg.ID       
                    )
                )
           )
       )
  ORDER BY 1;

I think, this should work with MySQL and do the job (but it's written
off the cuff, so take it with care ...).

* Problem
  -------

  Because MySQL can't use indexes yet, if there are OR terms, this
  query most probably will execute very slowly - it has to do full
  table scans and the joins can grow very large ...

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
EXISTS, NOT EXISTSJussi Jääskeläinen30 Aug
  • EXISTS, NOT EXISTSsinisa31 Aug
  • Re: EXISTS, NOT EXISTSMartin Ramsch31 Aug
    • Re: EXISTS, NOT EXISTSMichael Widenius31 Aug
  • EXISTS, NOT EXISTSMichael Widenius31 Aug
  • Re: EXISTS, NOT EXISTSJussi Jääskeläinen31 Aug
    • Re: EXISTS, NOT EXISTSJani Tolonen31 Aug
      • Re: EXISTS, NOT EXISTSBenjamin Pflugmann1 Sep
        • Re: EXISTS, NOT EXISTSJani Tolonen1 Sep
          • Re: EXISTS, NOT EXISTSMichael Widenius1 Sep
  • [Fwd: mysql 3.23.49 porting problem on POSIX 1003.1-2001 hosts]Michael Widenius2 Mar