Re: EXISTS, NOT EXISTS

[Michael Widenius]

>>>>> "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