List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:August 31 1999 12:06am
Subject:Re: EXISTS, NOT EXISTS
View as plain text  
>>>>> "Martin" == Martin Ramsch <m.ramsch@stripped> writes:

Martin> Long posting ahead ... be warned :-)
Martin> 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...

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

<cut>

Martin> * Problem
Martin>   -------

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

Hi!

Excellent mail Martin, except for the above little detail :)

MySQL can do indexes on OR as long as this is the same key (like in
the example query)

SELECT ... WHERE key1 = # or key1= #  will use indexes

SELECT ... WHERE key1 = # or key2= # will not use indexes.

Fortunately the other query can easily be written as:

CREATE TEMPORARY TABLE t1 SELECT ... WHERE KEY1 = #;
INSERT INTO t1 SELECT ... WHERE KEY2 = #;
SELECT ... FROM t1;
DROP TABLE t1;

Regards,
Monty

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