Back to Blogs
High-volume e-commerce catalogs suffer from high CPU usage during inventory checks. When search queries take seconds to load, client conversions drop. We resolved this for a major retail site by refactoring index structures and query routing.
Optimization Tactics
We implemented three core optimizations:
- Read Replicas: Routed all read-only search traffic to dedicated database replicas, keeping the master database free for writes.
- Compound Indexing: Configured compound SQL indexes on active filter combinations (e.g., category + availability).
- Redis Caching: Cached catalog results for 60 seconds to avoid repeating expensive joins.
Query Index Syntax
We created compound indexes to cover search filters:
CREATE INDEX idx_products_cat_avail
ON products (category_id, is_available)
INCLUDE (name, price);
Results
Average query resolution times fell from 850ms to 42ms. CPU load decreased by 70%, enabling the site to scale seamlessly during search peaks.