Clever Geek Handbook

SQLite join on the left with two conditions in the right table

I have two tables, a list of items and a sort order, defined for some group_id. Items either belong to a group or are shared (group_id = 0).

I want to query all general and group items with the correct sort order for this group. All items with a sort order of -1 must be dismissed. Items without an associated sort_order must be included.

Invalid (Naive) request:

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 an inner join — items without a matching sort_order are rejected.

Slow request:

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, is there a better / correct way to handle this?

+3


source to share


2 answers


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 the combined index worked for me:



CREATE INDEX sort_order_item_group 
ON sort_order (order_id, group_id);
0


source


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
+1


source






More articles:


All Articles