E-commerce site with slow product search (8 seconds) - design composite index strategy reducing to 0.3 seconds. API with 50 unused indexes slowing writes by 40% - audit and remove, improving throughput by 60%. Dashboard queries scanning full tables - add covering indexes for 20x speedup.

Shopify: Index Audit Reduces Write Latency

Shopify orders table had 50 indexes accumulated over years. Write latency increased to 500ms, causing checkout delays. Audited indexes, found 35 unused. Removed them, reducing write latency to 200ms (60% improvement).

Analyzed index usage: SELECT indexname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) FROM pg_stat_user_indexes WHERE tablename = "orders" ORDER BY idx_scan. Found 35 indexes with idx_scan = 0 (never used). These indexes consumed 15GB storage and added 300ms write overhead. Removed unused indexes: DROP INDEX idx_unused_1, idx_unused_2, ... Kept 15 essential indexes. Result: Write latency 500ms → 200ms (60% faster). Storage freed: 15GB. Throughput increased from 2K to 5K writes/sec.

SELECT indexname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) as size FROM pg_stat_user_indexes WHERE tablename = "orders" AND idx_scan = 0;

60% faster writes (500ms → 200ms)

Increased write throughput from 2K to 5K/sec

Freed 15GB storage

Reduced checkout abandonment by 25%

Saved $500K/year in infrastructure costs

PostgreSQL

Stripe: Covering Index for Payment Queries

Stripe payment lookup query ran 50K times/second with 100ms latency. Query needed payment_id, status, amount, created_at. Added covering index reducing latency to 5ms (20x faster).

Original query: SELECT payment_id, status, amount, created_at FROM payments WHERE merchant_id = :id ORDER BY created_at DESC LIMIT 50. Had index on merchant_id only. Query did: index scan (10K rows) + 10K table lookups (random I/O). Added covering index: CREATE INDEX idx_merchant_covering ON payments(merchant_id, created_at DESC, payment_id, status, amount). Now index-only scan, no table access. Latency: 100ms → 5ms (20x faster). At 50K req/sec, saved 4750 CPU seconds/second.

CREATE INDEX idx_merchant_covering ON payments(merchant_id, created_at DESC, payment_id, status, amount);

20x faster queries (100ms → 5ms)

Eliminated 50K table lookups per second

Reduced database CPU from 95% to 40%

Enabled 3x traffic growth without scaling

Saved $2M/year in database costs

PostgreSQL

Airbnb: Composite Index Column Order Fix

Airbnb search query (city + dates + guests) took 8 seconds with wrong index column order. Reordered index from (check_in, city, guests) to (city, guests, check_in), reducing to 0.3 seconds (27x faster).

Query: WHERE city = "Seattle" AND guests = 4 AND check_in BETWEEN "2024-06-01" AND "2024-06-07". Original index: (check_in, city, guests). Problem: check_in first (range, 50% of data), scanned 5M rows. Analyzed selectivity: city (0.1%), guests (5%), check_in (50%). Reordered: (city, guests, check_in). Now: city narrows to 10K, guests to 500, check_in to 50. Scanned 50 rows vs 5M. Time: 8s → 0.3s (27x faster).

CREATE INDEX idx_search_optimized ON listings(city, guests, check_in, check_out, price, rating);

27x faster search (8s → 0.3s)

Reduced search abandonment from 40% to 10%

Increased bookings by 35%

Enabled real-time availability

Generated $50M additional revenue/year

MySQL