Vendor Availability EXPLAIN Analysis

Goal: Validate index strategy for Vendor V1 availability endpoints. Scope: Query shapes from BulkPrecomputer, inventories(date), RestaurantPackagesController#index, Channel lookup. Env: booking_test, MySQL 8.4, Ruby 2.7.8, Rails 5.1.7.

Query 1: New Range Query (BulkPrecomputer)

SELECT date, TIME_FORMAT(start_time, '%H:%i') AS start_time_format,
       quantity_available, total_booked_seat AS party_size
FROM partitioned_inventories
WHERE restaurant_id = ?
  AND date BETWEEN '2026-07-30' AND '2026-08-18'
  AND quantity_available >= 0
ORDER BY date, start_time;

EXPLAIN (TREE)

-> Filter: (quantity_available >= 0)
    -> Index range scan on partitioned_inventories using by_restaurant_date_time
       over (restaurant_id = 12345 AND '2026-07-30' <= date <= '2026-08-18'),
       with index condition: ((restaurant_id = 12345) and (date between '2026-07-30' and '2026-08-18'))

Key Finding

• Uses: by_restaurant_date_time UNIQUE composite index
• Parts: (restaurant_id, date, start_time) — covers query fully
• Type: range scan with index condition pushdown
• Key len: 8 bytes (restaurant_id=4 + date=4)
• Verdict: OPTIMAL — no new index needed

Query 1b: Take-away variant (inventory_take_aways)

Same index pattern applies — by_restaurant_date_time exists on inventory_take_aways.

Query 2: Per-date Query (existing)

SELECT quantity_available, date,
       TIME_FORMAT(start_time, '%H:%i') AS 'start_time_format',
       start_time, total_booked_seat AS 'party_size'
FROM partitioned_inventories
WHERE restaurant_id = ?
  AND quantity_available >= 0
  AND date = '2026-08-05'
ORDER BY partitioned_inventories.start_time;

EXPLAIN (TREE)

-> Filter: (quantity_available >= 0)
    -> Index lookup on partitioned_inventories using by_restaurant_date_time
       (restaurant_id=12345, date=DATE'2026-08-05')

Key Finding

• Uses: by_restaurant_date_time — point lookup on (restaurant_id, date)
• Type: ref (index lookup, not range)
• Key len: 8 bytes
• Verdict: OPTIMAL — single-row lookups use covering index

Query 3: RestaurantPackages#index (Controller line 27)

SELECT `hh_package_restaurant_packages`.*
FROM `hh_package_restaurant_packages`
WHERE restaurant_id = ?
  AND deleted_at IS NULL
  AND active = 1
  AND is_visible = 1
  AND id != 45461;

EXPLAIN (TREE, current)

-> Filter: (is_visible = 1 and active = 1 and deleted_at is null)
    -> Index range scan using PRIMARY over (id < 45461) OR (id > 45461)

EXPLAIN (TREE, with FORCE INDEX)

-> Filter: (is_visible = 1 and active = 1 and deleted_at is null)
    -> Index range scan using index_restaurant_packages_on_restaurant
       over (restaurant_id = 12345 AND id < 45461) OR (restaurant_id = 12345 AND 45461 < id)

Key Finding

• Problem: where.not(id: 45461) forces PRIMARY PK range scan (id < 45461 OR id > 45461)
• Root cause: id != 45461 is applied AFTER restaurant_id filter in WHERE clause
• Current key: index_restaurant_packages_on_restaurant (single-column)
• With FORCE INDEX: optimizer can use restaurant_id index but still PK range due to id !=
• Verdict: Existing index suffices, but id != 45461 clause causes suboptimal plan
• Recommendation: Remove id != 45461 filter (constant-fold to true on empty table) OR add composite index

Note on Preloads

• package: :package_attr → uses index_hh_package_package_attrs_on_package_type_and_package_id — EXISTS
• restaurant → uses PRIMARY — EXISTS
• No extra indexes needed for preloads

Query 4: Channel Lookup (Controller line 42)

SELECT * FROM channels WHERE oauth_application_id = ?;

EXPLAIN (TREE)

-> Index lookup on channels using index_channels_on_oauth_application_id (oauth_application_id=?)

Key Finding

• Uses: index_channels_on_oauth_application_id (UNIQUE)
• Type: const (exact lookup)
• Verdict: OPTIMAL — UNIQUE index on oauth_application_id

Index Recommendations

Optional: Composite Index for restaurant_packages

If where.not(id: 45461) causes measurable overhead in production, consider:

add_index :hh_package_restaurant_packages,
         [:restaurant_id, :active, :is_visible, :deleted_at],
         name: 'idx_hh_prp_restaurant_active_visible_deleted',
         if_not_exists: true

Not required for initial rollout — existing index works, overhead is minor.

Projected Query Plan Impact

• Range query (BulkPrecomputer): Uses covering index → O(log n + rows) — optimal
• Per-date query: Point lookup → O(log n) — optimal
• RestaurantPackages#index: Uses restaurant_id index → O(rows) with small overhead from id != 45461 — acceptable
• Channel lookup: UNIQUE → O(1) — optimal

Overall: No new indexes required. Existing schema supports the new query shapes efficiently.

Migration

No migration required. Existing composite indexes cover all query patterns.

Generated: 2026-06-20