MySQL Table Partitioning Strategy Prompt
Design a RANGE, LIST, or HASH partitioning strategy for a large MySQL table with low-cost archival of old data
- Target user
- DBAs and backend engineers managing large, growing MySQL tables
- Difficulty
- Advanced
- Tools
- Claude, ChatGPT, Cursor
The prompt
You are a senior MySQL DBA who has partitioned multi-hundred-gigabyte tables in production and is designing a partitioning strategy that improves pruning and makes data retention cheap, without introducing query regressions. Be skeptical: partitioning helps only when queries and maintenance align with the partition key, so push back if the workload does not justify it. I will provide: - The table DDL (`SHOW CREATE TABLE`), including all indexes and the primary key: [PASTE] - The row count, approximate size, and growth rate: [DESCRIBE] - The dominant query patterns and their WHERE/ORDER BY clauses: [PASTE representative queries] - The retention policy and how old data is removed today: [DESCRIBE e.g. nightly DELETE of rows older than N days] - The MySQL version and any replication or foreign-key constraints: [DESCRIBE] Design in this order: 1. **Justify or reject partitioning.** Confirm the workload actually filters or ages out on a column that can become the partition key, and state plainly if conventional indexing or archival would serve better. 2. **Choose the scheme and key.** Recommend RANGE (typically on a date/time or auto-increment column), LIST, or HASH, and account for the InnoDB rule that every unique key — including the primary key — must contain the partitioning column. 3. **Define the partition layout.** Specify the partition boundaries (e.g. monthly RANGE partitions plus a `MAXVALUE` catch-all), the initial set, and the index changes the new primary/unique keys require. 4. **Verify pruning.** Show the `EXPLAIN ... PARTITIONS` (or `EXPLAIN FORMAT=JSON`) you expect for the dominant queries and confirm which partitions are pruned versus scanned; flag any common query that would now scan all partitions. 5. **Plan maintenance.** Define how new partitions are added ahead of time, how old data is dropped via `ALTER TABLE ... DROP PARTITION` instead of a large DELETE, and how to monitor partition row counts via `information_schema.PARTITIONS`. Output: the full `ALTER TABLE ... PARTITION BY` (or rebuild) statement, a maintenance schedule for adding and dropping partitions, the expected `EXPLAIN PARTITIONS` for the top queries, and a short list of queries that will not benefit. Guardrails: prototype the partitioned schema on a staging copy with production-representative data and run the real query workload against it before cutover, take a full backup before any `ALTER TABLE` that rebuilds the table, and validate the online DDL/locking behavior and replication impact on a replica first since partitioning a large table can be a long, write-heavy operation.
Why this prompt works
The most common partitioning mistake is adopting it as a reflex rather than a deliberate fit to the workload. This prompt opens by demanding justification: partitioning only pays off when queries filter on, or age out by, a column that can serve as the partition key. By making the model state plainly when ordinary indexing or a separate archive table would serve better, it avoids the trap of carving a table into partitions that every query then scans in full — which is slower than the original.
The design steps encode the constraints that actually bite in InnoDB. The rule that every unique key, including the primary key, must contain the partitioning column frequently forces a primary-key change, and that ripple touches application assumptions and foreign keys — so the prompt surfaces it early rather than letting it derail the cutover. Requiring the expected EXPLAIN ... PARTITIONS output for the dominant queries turns pruning from a hope into a verified property, and explicitly flagging queries that will now scan all partitions keeps the trade-off honest.
The retention and maintenance focus is where partitioning earns its keep operationally. Dropping an entire old partition with ALTER TABLE ... DROP PARTITION is near-instant and generates almost no undo, where the equivalent DELETE of millions of rows is slow, bloats the undo log, and lags replication. Pairing that with pre-creating future partitions and monitoring information_schema.PARTITIONS gives a sustainable lifecycle. Finally, the guardrails treat the initial partitioning as the major online schema change it really is: a backup, a staging rehearsal with representative data, and replica validation, because rebuilding a large table can hold locks and lag replicas for a long time.