sql - 选择所有存在于其他表中的行 - 如何提高性能？

Question

我在 SQL Server 2008 R2 中有三个表:

PRODUCTS (id int, title varchar(100), ....)  
WORDS (id int,word varchar(100) )  
WORDS_IN_TITLES (product_id int, word_id int)  

现在我想选择所有在标题中使用了特定词语的产品。

现在我这样做了:

declare  @words tp_intList  
insert into @words values(154)  
insert into @words values(172)  
declare @wordsCnt int = (select count(*) from @words)    

select * from products where id IN  
(
 select product_id from WORDS_IN_TITLES inner join 
 (select id from @words) wrds ON wrds.id=WORDS_IN_TITLES.word_id 
 group by product_id HAVING count(*)=@wordsCnt
)  

它可以工作，但速度很慢。表包含 600k 行，返回 3.5k 行大约需要 4 秒。我需要它远低于 1 秒。我怎样才能提高性能？

Answer 1

select products.*
    from products
        inner join (select p.id
                        from products p
                            inner join words_in_titles wit
                                on p.id = wit.product_id
                        where wit.word_id in (154,172)
                        group by p.id
                        having count(distinct wit.word_id) = 2) q
           on products.id = q.id