# Top Full-Scan Queries

## 1. Orders with all relations (no filters)

Source: `packages/db_helper_sqlite/src/admin-apis/order.ts:441-499` — `getAllOrders()`

```sql
select
  "id",
  "user_id",
  "address_id",
  "slot_id",
  "is_cod",
  "is_online_payment",
  "payment_info_id",
  "total_amount",
  "delivery_charge",
  "readable_id",
  "admin_notes",
  "user_notes",
  "order_group_id",
  "order_group_proportion",
  "is_flash_delivery",
  "created_at",
  (select json_array("id","name","email","mobile","created_at") from (select * from "users" where "users"."id" = "orders"."user_id" limit 1)) as "user",
  (select json_array("id","user_id","name","phone","address_line1","address_line2","city","state","pincode","is_default","latitude","longitude","google_maps_url","admin_latitude","admin_longitude","zone_id","created_at") from (select * from "addresses" where "addresses"."id" = "orders"."address_id" limit 1)) as "address",
  (select json_array("id","delivery_time","freeze_time","is_active","is_flash","is_capacity_full","delivery_sequence","group_ids") from (select * from "delivery_slot_info" where "delivery_slot_info"."id" = "orders"."slot_id" limit 1)) as "slot",
  (select coalesce(json_group_array(json_array("id","order_id","product_id","quantity","price","discounted_price","is_packaged","is_package_verified",(select json_array("id","name","short_description","long_description","unit_id","price","market_price","images","is_out_of_stock","is_suspended","is_flash_available","flash_price","created_at","increment_step","product_quantity","store_id",(select json_array("id","short_notation","full_name") from (select * from "units" where "units"."id" = "orders_orderItems_product"."unit_id" limit 1))) from (select * from "product_info" where "product_info"."id" = "orders_orderItems"."product_id" limit 1)))),json_array()) from "order_items" where "order_items"."order_id" = "orders"."id") as "orderItems",
  (select coalesce(json_group_array(json_array("id","order_time","user_id","order_id","is_packaged","is_delivered","is_cancelled","cancel_reason","is_cancelled_by_admin","payment_state","cancellation_user_notes","cancellation_admin_notes","cancellation_reviewed","cancellation_reviewed_at","refund_coupon_id")),json_array()) from "order_status" where "order_status"."order_id" = "orders"."id") as "orderStatus"
from "orders"
where "orders"."id" = "orders"."id"
order by "orders"."created_at" desc
limit ?
```

**Why it's bad**: `where "orders"."id" = "orders"."id"` is an always-true condition — it's a no-op placeholder. When no cursor/slot/filters are provided, this scans ALL orders with 6 levels of nested correlated subqueries, then filters out COD/pending payments in-memory after fetching everything.

---

## 2. Active slots (no date filter)

Source: `packages/db_helper_sqlite/src/admin-apis/slots.ts:32-47` — `getActiveSlotsWithProducts()`

```sql
select
  "id",
  "delivery_time",
  "freeze_time",
  "is_active",
  "is_flash",
  "is_capacity_full",
  "delivery_sequence",
  "group_ids",
  (select coalesce(json_group_array(json_array("product_id","slot_id",(select json_array("id","name","images") from (select * from "product_info" where "product_info"."id" = "deliverySlotInfo_productSlots"."product_id" limit 1)))),json_array()) from "product_slots" where "product_slots"."slot_id" = "deliverySlotInfo"."id") as "productSlots"
from "delivery_slot_info"
where "deliverySlotInfo"."is_active" = ?
order by "deliverySlotInfo"."delivery_time" desc
```

**Why it's bad**: Only filters `is_active = true`, no `deliveryTime > now` check. Returns past expired slots. Correlated subquery per slot for products.

---

## 3. All slots with products for cache (deep JOINs on every slot)

Source: `packages/db_helper_sqlite/src/stores/store-helpers.ts:236-258` — `getAllSlotsWithProductsForCache()`

```sql
select
  "id",
  "delivery_time",
  "freeze_time",
  "is_active",
  "is_flash",
  "is_capacity_full",
  "delivery_sequence",
  "group_ids",
  (select coalesce(json_group_array(json_array("product_id","slot_id",(select json_array("id","name","short_description","long_description","unit_id","price","market_price","images","is_out_of_stock","is_suspended","is_flash_available","flash_price","created_at","increment_step","product_quantity","store_id",(select json_array("id","short_notation","full_name") from (select * from "units" where "units"."id" = "deliverySlotInfo_productSlots_product"."unit_id" limit 1)),(select json_array("id","name","description","image_url","created_at","owner") from (select * from "store_info" where "store_info"."id" = "deliverySlotInfo_productSlots_product"."store_id" limit 1))) from (select * from "product_info" where "product_info"."id" = "deliverySlotInfo_productSlots"."product_id" limit 1)))),json_array()) from "product_slots" where "product_slots"."slot_id" = "deliverySlotInfo"."id") as "productSlots"
from "delivery_slot_info"
where ("deliverySlotInfo"."is_active" = ? and "deliverySlotInfo"."delivery_time" > ?)
order by "deliverySlotInfo"."delivery_time" asc
```

**Why it's bad**: Called on every slot lookup (`getSlotById`, `getProductSlots`, `getAllProductsSlots`, `getMultipleProductsSlots`) in `slot-store.ts`. Despite filtering for future active slots, it loads ALL of them with deep nested subqueries (product → unit + store) per product per slot, even when only one slot's data is needed.

---

## 4. Vendor orders (no WHERE at all)

Source: `packages/db_helper_sqlite/src/admin-apis/vendor-snippets.ts:171-187` — `getVendorOrders()`

```sql
select ... from "orders"
  left join "users" ...
  left join "order_items" ...
    left join "product_info" ...
      left join "units" ...
order by "orders"."created_at" desc
```

**Why it's bad**: Absolutely no WHERE clause. Fetches every single order with deep JOINs on every call.

---

## 5. Product availability (reads ALL products)

Source: `packages/db_helper_sqlite/src/user-apis/slots.ts:29-46` — `getProductAvailability()`

```sql
select "id", "name", "is_out_of_stock", "is_flash_available"
from "product_info"
where "product_info"."is_suspended" = ?
```

**Why it's bad**: Called on every `getSlotsWithProducts` page load. Reads every non-suspended product even though only products in active slots are relevant.

---

## 6. searchUsers (no search = all users, no limit)

Source: `packages/db_helper_sqlite/src/admin-apis/user.ts:197-216` — `searchUsers()`

```sql
select "id", "name", "mobile" from "users"
-- no WHERE, no LIMIT
```

**Why it's bad**: When called without a search term, returns the entire `users` table with no limit.

---

## 7. All cache initialization helpers

Source: `packages/db_helper_sqlite/src/stores/store-helpers.ts`

| Function | Line | Query |
|----------|------|-------|
| `getAllProductsForCache` | :86 | `select ... from product_info inner join units` — no WHERE |
| `getAllStoresForCache` | :115 | `select ... from store_info` — no WHERE |
| `getAllProductTagsForCache` | :159 | `select ... from product_tags inner join product_tag_info` — no WHERE |
| `getAllTagsForCache` | :187 | `select ... from product_tag_info` — no WHERE |
| `getAllTagProductMappings` | :200 | `select ... from product_tags` — no WHERE |
| `getAllUserNegativityScores` | :269 | `select ... from user_incidents group by user_id` — no WHERE |

**Why it's bad**: Individually these are meant for cache warming, but they're also called on every cache read miss in `product-store.ts`, doing 5 full table scans per product detail page view.