sql - 为什么此SQL查询这么慢？

Question

我正在运行此查询：

SELECT DISTINCT "items"."id" 
FROM "items" 
LEFT OUTER JOIN "item_explicit_mods" ON "item_explicit_mods"."item_id" = "items"."id" 
LEFT OUTER JOIN "explicit_mods" ON "explicit_mods"."id" = "item_explicit_mods"."explicit_mod_id" 
LEFT OUTER JOIN "item_implicit_mods" ON "item_implicit_mods"."item_id" = "items"."id" 
LEFT OUTER JOIN "implicit_mods" ON "implicit_mods"."id" = "item_implicit_mods"."implicit_mod_id" 
LEFT OUTER JOIN "shops" ON "shops"."id" = "items"."shop_id" 
WHERE ((item_explicit_mods.explicit_mod_id = 35 
        AND item_explicit_mods.primary_value >= 5 AND item_explicit_mods.primary_value <= 6) 
    OR (item_explicit_mods.explicit_mod_id = 48)) 
GROUP BY items.id 
HAVING COUNT(item_explicit_mods.id) = 2 
ORDER BY "items"."created_at" 
ASC LIMIT 100

Sqlite解释产生此

0|0|0|SCAN TABLE items USING INTEGER PRIMARY KEY (~1000000 rows)
0|1|1|SEARCH TABLE item_explicit_mods USING AUTOMATIC COVERING INDEX (explicit_mod_id=?) (~7 rows)
0|1|1|SEARCH TABLE item_explicit_mods USING AUTOMATIC COVERING INDEX (explicit_mod_id=?) (~7 rows)
0|2|2|SEARCH TABLE explicit_mods USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0|3|3|SEARCH TABLE item_implicit_mods USING AUTOMATIC COVERING INDEX (item_id=?) (~7 rows)
0|4|4|SEARCH TABLE implicit_mods USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0|5|5|SEARCH TABLE shops USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0|0|0|USE TEMP B-TREE FOR DISTINCT
0|0|0|USE TEMP B-TREE FOR ORDER BY

此查询耗时> 10秒。知道为什么要对主键进行扫描吗？

Answer 1

尝试将查询更改为此

SELECT "items"."id" 
FROM "items" 
INNER JOIN "item_explicit_mods" ON "item_explicit_mods"."item_id" = "items"."id" AND (((item_explicit_mods.explicit_mod_id = 35 
        AND item_explicit_mods.primary_value >= 5 AND item_explicit_mods.primary_value <= 6) 
    OR (item_explicit_mods.explicit_mod_id = 48)) )
LEFT OUTER JOIN "explicit_mods" ON "explicit_mods"."id" = "item_explicit_mods"."explicit_mod_id" 
LEFT OUTER JOIN "item_implicit_mods" ON "item_implicit_mods"."item_id" = "items"."id" 
LEFT OUTER JOIN "implicit_mods" ON "implicit_mods"."id" = "item_implicit_mods"."implicit_mod_id" 
LEFT OUTER JOIN "shops" ON "shops"."id" = "items"."shop_id"  
GROUP BY items.id 
HAVING COUNT(item_explicit_mods.id) = 2 
ORDER BY "items"."created_at" 
ASC LIMIT 100