Creating & Managing Partitioned Tables

Creating a partitioned table is only the beginning. As time progresses, old partitions must be retired, new date brackets must be provisioned, and data must be archived.

MySQL provides specialized ALTER TABLE commands for partition maintenance that run in seconds rather than hours.

1. Instant Data Purging with DROP PARTITION

In standard unpartitioned tables, deleting 50 million old rows via DELETE FROM logs WHERE log_date < '2023-01-01'; is disastrous:

• Generates 50 million undo log entries
• Locks table rows for hours
• Squeezes disk I/O and leaves fragmented table space

With Range Partitioning, purging an entire year's data is an instant DDL metadata drop:

2. Adding New Partitions (ADD PARTITION)

If your table has a MAXVALUE partition, ADD PARTITION fails. You must use REORGANIZE PARTITION to split the catch-all:

3. Fast Data Archiving with EXCHANGE PARTITION

MySQL allows you to swap an entire partition with a standalone table with zero data copying:

Now, orders_archive holds all 2024 records as an independent table ready for export to S3 or cold storage, with zero downtime!

4. Inspecting Partition Metadata

Multiple Choice Questions

1. Why is ALTER TABLE ... DROP PARTITION vastly superior to DELETE FROM table WHERE date < ...?

A. It bypasses disk writes B. It instantly removes the physical partition file from disk in milliseconds without generating undo logs or row locks C. It requires no permissions D. It keeps data in the recycle bin Answer: B Explanation: DROP PARTITION is an instantaneous DDL operation that unlinks disk files directly, avoiding massive row-by-row undo logging.

2. What command splits an existing MAXVALUE partition to accommodate a new year bracket?

A. SPLIT PARTITION B. REORGANIZE PARTITION C. MODIFY PARTITION D. EXPAND PARTITION Answer: B Explanation: REORGANIZE PARTITION splits or merges existing partitions into new definitions without losing stored data.

3. What does ALTER TABLE ... EXCHANGE PARTITION accomplish?

A. Deletes the partition permanently B. Swaps the physical data of a partition with an identically-structured standalone unpartitioned table via metadata pointer exchange C. Converts range partition to hash partition D. Re-indexes the table Answer: B Explanation: EXCHANGE PARTITION swaps a partition's data with an unpartitioned table near-instantaneously without data copying.

4. In which system catalog table can you inspect row counts and disk sizes per partition?

A. mysql.partitions B. information_schema.partitions C. performance_schema.tables D. sys.partition_stats Answer: B Explanation: information_schema.partitions provides granular storage metadata including table_rows and data_length for each partition.

5. What statement completely strips partitioning from a table while retaining all data in a single unified table?

A. DROP PARTITIONING B. ALTER TABLE my_table REMOVE PARTITIONING; C. UNPARTITION my_table; D. MERGE ALL PARTITIONS; Answer: B Explanation: ALTER TABLE ... REMOVE PARTITIONING converts a partitioned table into a standard monolithic InnoDB table.