sql查询工作非常缓慢. [英] Sql query working very slow.

问题描述

专家，

有2个表emailinfo(id，email)和keywordinfo(id，keyword).
emailinfo包含80,000行，而keywordinfo包含2000行.


我想要来自emailinfo表的电子邮件，其中不包含来自keywordinfo表的关键字.

我的查询如下所示.

Hi Expert,

There 2 tables emailinfo(id,email) and keywordinfo(id,keyword).
emailinfo contains 80,000 rows and keywordinfo contains 2000 rows.


I want emails from emailinfo table which does not contains keywords from keywordinfo table.

And my query is like following.

SELECT  EMAIL.email FROM emailinfo EMAIL    WHERE   (  not exists( Select keyword from keywordinfo where EMAIL.email  like '%'+ keyword +'%' ))

但是执行需要3分钟.

请帮助我解决这个问题.

在此先谢谢您.

but it takes 3 minutes to execute .

please help me to slove this problem.

Thanks in advance.

推荐答案

我想要来自emailinfo表的电子邮件，其中没有包含来自keywordinfo表的关键字.

I want emails from emailinfo table which does not contains keywords from keywordinfo table.

SELECT  [email]
FROM emailinfo
WHERE NOT [email] IN(SELECT [keyword] AS [email]
                    FROM keywordinfo
                    WHERE [keyword] like '%'+ keyword +'%' )

WITH keywords AS
(
  SELECT
    keyword
  FROM
    keywordinfo
)

SELECT
  emailinfo.email
FROM
  emailinfo
WHERE
  emailinfo.email NOT LIKE '%' + keywords.keyword + '%'

该查询将始终花费很长时间.您的解决方案需要不同的设计:当收到一封新电子邮件时，检查它的关键字，然后将新条目添加到包含emailid和keywordid的链接表中.在电子邮件到达时执行此额外步骤所需的时间并不重要.以后再搜索时，不再需要全文搜索.

That query will always take a long time. You need a different design for your solution: When a new email arrives, check it for the keywords, and add new entries to a linking table containing emailid and keywordid. The time required for doing that extra step at email arrival does not matter. When you search later on, no full text search is required anymore.

这篇关于sql查询工作非常缓慢.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！