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?
Aucun commentaire:
Enregistrer un commentaire