In one of our recent performance firefights, we ran into a problem that’s likely familiar to anyone working with high-throughput systems: a single MySQL table had quietly become a bottleneck for our entire application. Here’s how we identified the issue, solved it with MySQL partitioning and indexing, and what we learned along the way.

The Problem

We had a table that was ingesting over 2 million rows per day. It used a composite primary key, say columns (A, B), but many of our queries targeted only A or only B. As the table grew, performance degraded. Queries on non-leading columns of the composite key slowed significantly.

Eventually, this table became the primary reason for degraded performance under high load across the system. We saw high query latencies and elevated CPU usage on the database. Something had to change.

Our first step was renaming the old table and creating a new one with the same schema, but partitioned by timestamp. We used weekly partitions, so each held roughly 14 million rows.

However, MySQL requires the partition key to be part of the primary key. This meant updating our primary key to include three columns: (A, B, timestamp).

And that’s when things got tricky.

A New Bottleneck

Post-migration, our application started querying based on the middle column of the new three-part primary key. This led to slow lookups under high load:

• Queries scanning even half a million rows took 10+ seconds.
• CPU utilization hit 100%.
• Timeouts and errors became frequent.

So while the partitioning solved one problem, it created another.

The Real Fix: Proper Indexing

At this point, clients had started noticing the latency, and we had to fix the issue immediately. We added a secondary index on the second column of the primary key, the one most frequently used in queries.

The results were immediate:

• Query time dropped drastically.
• CPU usage fell from 100% to a stable ~20%. (This was so magical to see)
• The app returned to normal performance.

Since MySQL maintains indexes per partition, the impact on insert performance was minimal, a nice bonus.

Why Partitioning Still Matters

Despite the indexing fix, partitioning brought long-term benefits:

• Old partitions can be archived or dropped without affecting recent data.
• Data lifecycle management is now simpler and more scalable.

We’re also planning to refactor queries to include the timestamp column, which will help MySQL prune partitions and narrow down the search range.

We also considered:

• Creating a mapping table to reduce lookup complexity.
• Offloading parts of the workload to Amazon DynamoDB for key-value access.

These are more invasive changes and are now scheduled for a future maintenance window.

Key Takeaways

• This problem shouldn’t have happened, but it slipped through. Be extra careful when reviewing query patterns and access paths during design and code reviews.
• Composite primary keys can become performance bottlenecks, especially when queries don’t use the leading columns.
• Always monitor real query patterns, designing schemas based on theory alone is risky.