sql - 用户独有的词 - 不在

Question

我这里有一个非常简单的表结构。只是与简单 user_id 相关的单词列表。

Word Table: 
    word - varchar(50)
    user_id - integer

我需要找到一个用户使用但其他用户未使用的词。目前我正在这样做，它在 Postgresql (9.0.3) 上以 200k 字(~.3-.5 秒)运行良好，并且在具有相同数据(5 分钟以上)的 MySQL (5.1.54) 上完全失败仍在运行)。所有使用的列都已编入索引。

SELECT  
    word, count(word) as count
FROM 
    words
WHERE         
    word not in (select word from words where user_id <> 99 group by word)
    and user_id = 99
GROUP BY word
ORDER BY count desc LIMIT 20

1) 有人知道更好的方法吗？

2) 有人知道为什么它在 MySql 上完全失败吗？

编辑:这修复了 MySQL 上的问题，从 5 分钟以上到 10-20 毫秒 - 感谢 Borealid

SELECT  
    word, count(word) as count
FROM 
    words
WHERE         
    word not in (select distinct word from words where user_id <> 99)
    and user_id = 99
GROUP BY word
ORDER BY count desc LIMIT 20

谢谢。

Answer 1

尝试 NOT EXISTS():

SELECT  
    w1.word, 
    COUNT(w1.word) as count
FROM 
    words w1
WHERE         
    NOT EXISTS (
        SELECT 1 
        FROM 
            words w2 
        WHERE 
            w2.user_id <> 99 
        AND 
            w1.word = w2.word 
        )
AND 
    w1.user_id = 99
GROUP BY 
    w1.word
ORDER BY 
    count DESC 
LIMIT 20;

确保您在 user_id 和单词(或组合)上有一个索引，使用解释来查看查询计划以及最适合您的内容。

======编辑:也可以尝试使用 IS NULL 的 LEFT JOIN 解决方案:

SELECT
    w1.word,
    COUNT(w1.word) AS count
FROM
    words w1
        LEFT JOIN words w2 ON (w1.word = w2.word AND w1.user_id <> w2.user_id)
WHERE
    w1.user_id = 99
AND
    w2.word IS NULL
GROUP BY
    w1.word
ORDER BY 
    count DESC 
LIMIT 20;

在两列上尝试索引:

CREATE INDEX idx_word_user ON words ( word, user_id);