From 2-Minute Reports to 30-Second Queries in Production

MOS reports run every morning. Every manager in every brand checks stock levels, sales performance, and delivery status before the day starts. When these reports take 2 minutes to load, that delay adds up across a team across 8 brands.

The reports were not a special edge case. They were core daily operations. Fixing them was a priority.

Problem

I ran EXPLAIN on the slowest endpoint and found three issues immediately.

Full table scan on the transactions table. The WHERE clause filtered by store_id and transaction_date. Neither column had an index. MySQL was reading the full table for every request.

N+1 pattern in the report logic. The original code fetched a list of stores in one query, then ran a separate aggregation query for each store. At 100 stores, that is 101 database round trips per page load. The page waited for each one in sequence.

Type mismatch on a JOIN column. The inventory table and transactions table both had a product identifier but one was stored as VARCHAR and the other as INT. MySQL could not use any index on that join because of the implicit cast on every row comparison.

Solution

Added composite indexes where the query patterns actually needed them:

• INDEX (store_id, transaction_date) on transactions
• INDEX (store_id, stock_level, is_active) on inventory
• INDEX (is_active, region, store_id) on stores

Rewrote the N+1 endpoint as a single JOIN query with GROUP BY. The 101 queries became one.

Fixed the type mismatch. Normalized the product identifier to INT across both tables, updated the application query to match.

Added server-side pagination at 50 stores per page so the query scope stays bounded.

Added React Query caching on the frontend with a 5-minute stale window. The same report opened twice in 5 minutes hits the cache, not the database.

Result

Heavy reports dropped from 2 minutes to 30 to 40 seconds.

The N+1 fix cut database queries per page load from 101 to 1 for the main report endpoint.

Frontend caching reduced redundant API calls by about 70% across all dashboard views.

The gains came from reading what the database was actually doing, not from rewriting application logic. EXPLAIN shows the problem. The fix is usually straightforward once you can see it.