sqlite索引 where条件 联合索引 组合索引 Multicolumn Indexes

DELETE 
FROM ngrams_2 
WHERE EXISTS (
    SELECT 1 
    FROM ngrams_3 
    WHERE ngrams_2.word1 = ngrams_3.word1 
    AND ngrams_2.word2 = ngrams_3.word2
    UNION ALL
    SELECT 1 
    FROM ngrams_3 
    WHERE ngrams_2.word1 = ngrams_3.word2 
    AND ngrams_2.word2 = ngrams_3.word3
);

 

这样可以用到多列索引 2秒

 

DELETE FROM ngrams_2
WHERE EXISTS (
    SELECT 1
    FROM ngrams_3
    WHERE (
        ngrams_2.word1 = ngrams_3.word1
        AND ngrams_2.word2 = ngrams_3.word2
    )
    OR (
        ngrams_2.word1 = ngrams_3.word2
        AND ngrams_2.word2 = ngrams_3.word3
    )
);

 

这样只能用到单列索引，很慢 10分钟还没好（更，奇怪，我重新建了一次索引，又可以了