CODEWITHSUNDEEP
sqlsqlitequery-optimization
Nick Barrett
Nick Barrett

Reputation: 1051

Why is this SQL query SO slow?

I am running this query:

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 explain is producing this

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

This query is taking > 10 seconds to run. Any idea why it is doing a SCAN on a primary key?

Upvotes: 1

Views: 105

Answers (2)

Rodion
Rodion

Reputation: 886

Try to change your query to this

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

Upvotes: 1

Alberto Solano
Alberto Solano

Reputation: 8227

Any idea why it is doing a SCAN on a primary key?

I think because you're doing this:

SELECT DISTINCT "items"."id"

The column id of that table is a primary key. You're looking for the distinct identifiers referencing other tables in the joins. For this reason, there is a SCAN on the primary key.

Moreover, why are you grouping the results doing GROUP BY items.id while you're just looking for distinct identifiers?

Upvotes: 1

Related Questions