Hi Robert,

>
> your table structure does not seem suitable for an exists query.
> "IN" querys were never ever optimised.

Oh... I think found somewhere a posting about this... but I'm not sure...

I have an Index on the main table:

ARTICLE.DEFINEID

this is used correctly to select the values in the ARTICLE Table... the result of the "EXIST" or "IN" statement.

In the second part of the query the result "ARTICLE.RELID" are used to select the Values in ARTICLE_CONTENTTREE_LINK by selecting ARTICLE_CONTENTTREE_LINK.ARTICLEID. There is an INDEX on ARTICLEID:

CREATE UNIQUE INDEX IDX_ARTICLE_CONTENTTREE_LINK ON ARTICLE_CONTENTTREE_LINK (ARTICLEID, TREEID);

Why does the exist query don't use this index in the secord part of the query ?

Nils

>
> Here is proper syntax for EXISTS:
>
> SELECT *
> FROM TABLE1
> WHERE( EXISTS
> ( SELECT *
> FROM TABLE2
> WHERE( TABLE1.KEY1 = TABLE2.KEY1 )
> AND( TABLE1.KEY2 = TABLE2.KEY2 )))
>
> ie. there needs to be matching indexes for optimization.
>
> Although why don't you do the below instead?
>
>
> SELECT L.*
> FROM ARTICLE_CONTENTTREE_LINK L
> JOIN ARTICLE A
> ON( A.DEFINEID = 112435 )
> AND( A.RELID = L.ARTICLEID )
>
> Kind Regards,
>
> Robert.
>