When an eCommerce platform slows down, the database is usually blamed and rarely examined. Teams reach for bigger instances or more replicas before anyone has looked at what MySQL is actually doing. In most audits we run, the real bottlenecks are structural — and fixable without new hardware.
The usual suspects, in order of frequency
1. Query multiplication (N+1)
The single most common finding: a page that should run 10 queries runs 400, because code loads a list and then queries per item. ORMs make this easy to write and easy to miss. It hides in development (small datasets, no latency) and explodes in production.
2. Indexes that do not match the queries
Tables often have many indexes — just not the composite ones the hot queries actually need, in the right column order. Meanwhile, redundant indexes slow every write. The fix starts with the slow query log and EXPLAIN, not with intuition.
3. Hot tables under lock pressure
Stock, sessions and counters are updated by every request during peaks. Row lock contention on these tables produces the characteristic “everything is slow but nothing is busy” symptom. Solutions are architectural: move counters to Redis, batch stock updates, rethink write patterns.
4. The catalog query that does everything
Filtered, sorted, paginated category listings joining five tables are among the most expensive queries in commerce. Beyond a certain catalog size, this workload belongs in a search engine — Elasticsearch or OpenSearch — not in MySQL.
Measure before touching anything
Enable the slow query log with a low threshold. Aggregate by normalized query, not by single execution — a 50ms query running 200 times per page beats any 2-second report query as an optimization target. This is where a statistical mindset pays off: optimize the distribution, not the anecdote.
Takeaway
Database performance work is diagnosis first, changes second. The teams that skip diagnosis buy bigger instances every year and keep the same problems — at higher cost.
