How to Partition a 50‑Million‑Row MySQL Table and Add Indexes Without Downtime

Background

The system contains a historical log table that records API call traces. The table was created without indexes on its key columns and has grown to about 50 million rows, making queries extremely slow.

Problem with ALTER

In MySQL 5.5 and earlier, ALTER TABLE on a large table rebuilds the table and locks it, which is unacceptable in production. MySQL 5.6 introduced Online DDL, allowing concurrent SELECT/INSERT/UPDATE/DELETE, but for tables larger than one million rows the operation is still risky and can cause crashes when adding an index.

Solution Overview

The chosen approach is to partition the data into several smaller tables, each with the required indexes. The process consists of:

Create a new table with the same schema and the missing indexes, and switch the application to write to this new table.

Back up the old table’s data for possible recovery.

Migrate the old data in 10 million‑row batches to separate partition tables (e.g., account_log_1, account_log_2, …), each fully indexed.

Backup Methods

Several backup options were evaluated:

COPY using CREATE TABLE … SELECT * – quick for testing.

Export via Navicat, generating INSERT statements.

mysqldump (not available on the production server).

Example COPY command:

create table account_log_1 select * from account_log;

In a test environment this took roughly one hour. Navicat export produced a 38.5 GB SQL dump, which was later compressed to 3.8 GB.

Creating Partition Tables

First copy the table structure without data and add the missing indexes: create table account_log_1 like account_log; After verifying primary keys and indexes, create additional tables ( account_log_2, account_log_3, …) in the same way.

Data Migration

Insert the first 10 million rows into the first partition:

INSERT INTO account_log_1 SELECT * FROM account_log WHERE id <= 10000000;

This operation completed in about 205 seconds (≈3 minutes 25 seconds). Extrapolating, moving the full 50 million rows in similar batches would require roughly 18 minutes.

Validate the migration by comparing row counts:

select count(1) from account_log_1;

select count(1) from account_log where id <= 10000000;

Counts matched, confirming a successful transfer.

Optionally delete the migrated rows to free space and improve query performance: delete from account_log where id <= 10000000; Subsequent batches use adjusted ID ranges. Example for the second partition:

INSERT INTO account_log_2 SELECT * FROM account_log WHERE id > 10000000 AND id <= 20000000;

Repeat the insert‑validate‑delete cycle until all partitions are populated.

Performance Checks

Counting rows in a partition takes about 1.8 seconds. COUNT(*) and COUNT(id) show similar performance on 10 million rows. Repeated queries benefit from MySQL’s caching, reducing execution time on subsequent runs. An indexed lookup on 10 million rows returns results in roughly 800 ms, demonstrating a substantial speedup compared with full‑table scans.

Practical Considerations

Export time and resulting file size can become bottlenecks; compression may be necessary.

Export operations may acquire locks, risking deadlocks during concurrent writes.

Monitoring I/O, bandwidth, and memory usage during export is essential to avoid resource exhaustion.

Special column types such as BLOB require careful handling to prevent data loss during import.

Different storage engines can affect import performance and compatibility.