SQL Optimization

From: Gilles WIART <gilles.wiart_at_alternative.fr>
Date: Thu, 15 Nov 2001 18:56:39 +0100
Message-ID: <9t0vke$2m2b$1@news4.isdnet.net>

Hello,

I have a simple question about SQL optimization. I would like to know why this SQL query

select count(*) from notices

        where code in (
            select code from index_motscles where (idx like 'FRANCAIS')
                 intersect
            select code from notices where contains(titre, 'eleves') > 0
         )

is faster (really faster) than this one :

select count(*) from notices

        where
            code in ( select code from index_motscles where (idx like
'FRANCAIS') )
                and
            contains(titre, 'eleves') > 0

The result is the same, but the execution time of the first query is 1 second, and the execution time of the second one is 20 seconds !!!

Do I need to use "intersect" everytime to optimize my queries ?

Thanks for your answers !

GilloS Received on Thu Nov 15 2001 - 11:56:39 CST