需要优化“不在”方面的帮助询问

发布于 2024-12-01 15:51:44 字数 425 浏览 3 评论 0原文

我有一个想要优化的 SQL 查询。

SELECT * 
FROM QUEUE_SMS_ALERT Q1
where ALERT_ORIGIN = "FOO" 
AND RECORD_ID is null 
and PHONE NOT IN (
    SELECT DISTINCT PHONE 
    FROM QUEUE_SMS_ALERT Q2 
    where Q2.ALERT_ORIGIN = "BAR"
);

基本上需要获取 ALERT_ORIGIN 为“FOO”的所有行，这些行在同一个表中没有 ALERT_ORIGIN“BAR”的对应行。该表包含大约 17000 行，并且只有大约 1000 条带有 ALERT_ORIGIN“BAR”的记录。所以我的查询应该给我大约 16000 行。

编辑：当前查询非常慢。我目前没有任何索引。

原文

I have an SQL query that I am looking to optimize.

SELECT * 
FROM QUEUE_SMS_ALERT Q1
where ALERT_ORIGIN = "FOO" 
AND RECORD_ID is null 
and PHONE NOT IN (
    SELECT DISTINCT PHONE 
    FROM QUEUE_SMS_ALERT Q2 
    where Q2.ALERT_ORIGIN = "BAR"
);

Basically need to get all rows where ALERT_ORIGIN is "FOO" Which do not have a corresponding row in the same table with ALERT_ORIGIN "BAR". The table contains abt 17000 rows and there are only abt 1000 records with ALERT_ORIGIN "BAR". So my query is supposed to give me abt 16000 rows.

EDIT : The current query is very slow. I do not have any indexes currently.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

青衫负雪 2024-12-08 15:51:44

我猜测您的电话列中有 NULL 值，这意味着 NOT IN 不起作用（所以它是“修复”而不是“优化”）。所以我用 NOT EXISTS 编写它：

SELECT *
FROM QUEUE_SMS_ALERT Q1 
WHERE
    Q1.ALERT_ORIGIN = 'FOO'
    AND
    Q1.RECORD_ID is null
    AND 
    NOT EXISTS (SELECT *
        FROM QUEUE_SMS_ALERT Q2
        WHERE 
            Q2.ALERT_ORIGIN = 'BAR'
            AND
            Q1.PHONE = Q2.PHONE)

如果它很慢而不是“错误”，那么您需要使用索引。你现在有什么？
对于此查询，您需要 (ALERT_ORIGIN, PHONE, RECORD_ID) 上的索引。

注意：字符串分隔符使用单引号

I'm guessing that you have NULL values in the phone column which means NOT IN doesn't work (so it's "fix" not "optimise"). So I've written it with NOT EXISTS:

SELECT *
FROM QUEUE_SMS_ALERT Q1 
WHERE
    Q1.ALERT_ORIGIN = 'FOO'
    AND
    Q1.RECORD_ID is null
    AND 
    NOT EXISTS (SELECT *
        FROM QUEUE_SMS_ALERT Q2
        WHERE 
            Q2.ALERT_ORIGIN = 'BAR'
            AND
            Q1.PHONE = Q2.PHONE)

If it is slow rather than "wrong" then you need to use indexes. What do you have now?
For this query, you need an index on (ALERT_ORIGIN, PHONE, RECORD_ID).

Note: use single quotes for string delimiters

回复收藏 0 原文

~没有更多了~