CODEWITHSUNDEEP
sqliteleft-join
kh0r
kh0r

Reputation: 101

SQLite left join with two conditions in right table

I have two tables: a list of items and the sort order scoped on some group_id. Items belong either to a group or are common (group_id=0).

I want to query all common and group items with the correct sort order for this group. All items with sort order -1 must be dismissed. Items with no associated sort_order must be included.

Wrong (Naive) Query:

SELECT items.* 
FROM   items LEFT JOIN sort_order ON items._id = sort_order.item_id 
WHERE  (items.group_id=0 OR items.group_id=14) 
  AND  sort_order.entity_id=14 
  AND  sort_order.sort >= 0

Behaves like a inner join - items with no corresponding sort_order are dismissed.

Slow Query:

SELECT items.* 
FROM   items LEFT JOIN sort_order 
           ON items._id = sort_order.item_id AND sort_order.entity_id=14 
WHERE  (items.group_id=0 OR items.group_id=14) 
  AND  sort_order.sort >= 0

With ~5.000 entries in sort_order and ~1500 items the query takes ~2 seconds.

My question: Is there a better/correct way to handle this?

Upvotes: 3

Views: 1578

Answers (2)

kh0r
kh0r

Reputation: 101

Found the source of the slow query: I forgot to create an index containing both sort_order.item_id and sort_order.item_id.

Adding a combined index did the trick for me:

CREATE INDEX sort_order_item_group 
ON sort_order (order_id, group_id);

Upvotes: 0

BvuRVKyUVlViVIc7
BvuRVKyUVlViVIc7

Reputation: 11811

Maybe nonsense:

SELECT items.* FROM items WHERE items.id not in (Select id from sort_order)

UNION

SELECT items.* FROM items INNER JOIN sort_order ON items._id = sort_order.item_id AND sort_order.entity_id=14 WHERE (items.group_id=0 OR items.group_id=14) AND sort_order.sort >= 0

Upvotes: 1

Related Questions