Scaling MySQL for transactional platforms: indexes, replicas and partitioning

Scaling MySQL for a transactional platform is mostly a sequence of disciplined steps: efficient queries and indexes first, then connection management, read replicas, safe schema changes and data lifecycle — with sharding as a last resort. Bookings, payments and orders depend on consistency, so the goal is to grow capacity without giving that up.

Step 1: find and fix slow queries

Most database problems are query problems. Enable the slow query log, aggregate it by query pattern, and fix the top offenders. For each, run EXPLAIN (or EXPLAIN ANALYZE) and look for full table scans, filesorts on large sets and temporary tables.

Step 2: design indexes for real queries

InnoDB stores rows in primary-key order, and secondary indexes point back to the primary key. Practical rules:

• Composite index order matters — equality columns first, then range or sort columns: an index on (tenant_id, status, created_at) serves “tenant’s pending orders, newest first”.
• Covering indexes — include the selected columns so the query never touches table rows.
• Short, stable primary keys — every secondary index contains the primary key; huge or random keys inflate all of them.
• Remove unused indexes — each one slows writes.

Step 3: control connections and transactions

• Use connection pooling in the application or a proxy, rather than opening a connection per request.
• Keep transactions short; never hold one open while calling an external supplier or payment API. That pattern produces lock waits and deadlocks under load. Use the state-machine approach described in idempotency in booking and payment APIs instead.
• Set statement timeouts for reporting queries.

Step 4: add read replicas

Replicas handle read-heavy traffic: search pages, dashboards, reports, exports. Two cautions:

• Replication lag — a user who just made a booking should read it from the primary, or from a replica confirmed to be caught up. Route “read-your-writes” paths to the primary.
• Reporting isolation — send heavy analytics to a dedicated replica so it never competes with checkout traffic.

Step 5: change schemas safely

Large tables make schema changes risky. Use InnoDB online DDL where supported, or tools like gh-ost and pt-online-schema-change for operations that would lock the table. Combine with expand-and-contract migrations: add the new column, deploy code that writes both, backfill, switch reads, then remove the old column in a later release. The deployment side is covered in zero-downtime deploys.

Step 6: manage data lifecycle

Transactional tables grow forever unless you decide otherwise:

• Archive completed historical records to separate tables or storage.
• Partition very large tables by date range, so old partitions can be dropped instantly and queries prune to recent data. Partitioning requires the partition key in every unique key, so plan it early.
• Keep audit and log data out of the primary transactional database where possible.

Step 7: cache what does not need to be live

Content, configuration and reference data rarely need a database round-trip. A cache layer — see Redis beyond caching — removes a large share of read load.

Step 8: shard only with a clear key

Sharding splits data across independent database servers by a key, such as tenant. It works well when nearly every query includes that key, which is often true in multi-tenant SaaS. It adds significant operational and application complexity, so treat it as the final step, after the previous ones are exhausted.

Monitor the right signals

Query latency percentiles, slow-query counts, buffer pool hit rate, replication lag, lock waits, deadlocks and connection usage tell you where pressure is building before customers feel it.

The takeaway

MySQL scales a long way when queries, indexes and transactions are designed with care. Follow the sequence — queries, indexes, connections, replicas, safe migrations, lifecycle — and most platforms never need to shard at all.