single table parent child relationship query
I have written a query to get the items from the table which doesn’t have any child items. It’s working fine but is very slow. Any better/easier/optimized way to write the same thing?
select distinct id, (select count(i.item_id) from order_item as i where i.parent_item_id = o.item_id) as c from order_item as o where product_type = 'bundle' having c = 0 order by id desc limit 10; Few of the fields are these to get the idea of a structure
Table: order_item Columns: item_id PK order_id parent_item_id product_id product_type item_id | order_id | parent_item_id | product_id | product_type ----------------------------------------------------------------- 1 | 1 | null | 1 | bundle 2 | 1 | 1 | 2 | simple 3 | 1 | 1 | 3 | simple 4 | 1 | null | 4 | bundle 5 | 2 | null | 1 | bundle 6 | 2 | 5 | 2 | simple 7 | 2 | 5 | 3 | simple Query should only return the 4rth item
Try below. Also consider creating indexes on PARENT_ITEM_ID and ITEM_ID
SELECT OI.* FROM ORDER_ITEM OI LEFT JOIN ORDER_ITEM OI2 ON OI2.PARENT_ITEM_ID = OI.ITEM_ID WHERE OI.PRODUCT_TYPE = 'bundle' AND OI2.PARENT_ITEM_ID IS NULL 
I would suggest not exists:
select oi.* from order_item oi where oi.product_type = 'bundle' and not exists (select 1 from order_item oi2 where oi2.parent_item_id = oi.item_id and oi2.product_type = 'bundle' ) order by id desc limit 10; For performance, you want an index on order_item(parent_item_id, product_type).
Note: I’m not sure you want the product_type filter in the subquery, but it is the logic your query is using.
