[firebird-support] Re: Firebird 2.5: Optimize of IN or EXISTS performance

[sir_wally_lewis]

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

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.