Tagged articles
Large Tables
38 articles · Page 1 of 1
Adding a column to a MySQL table with tens of millions of rows can lock the table for minutes or even hours, disrupting services, so this article evaluates six practical approaches—including native online DDL, offline maintenance, PT‑OSC, logical migration with dual‑write, gh‑ost, and partition sliding‑window—detailing their mechanisms, trade‑offs, and suitable scenarios.
Large TablesMySQLdatabase operations
0 likes · 13 min read

This article presents a five-step practical guide for optimizing MySQL queries on tables exceeding 100 million rows, covering index optimization, SQL rewriting, cursor-based pagination, table structure improvements including partitioning, and architectural upgrades like read-write separation and sharding, with concrete code examples and performance comparisons.
Index OptimizationLarge TablesMySQL
0 likes · 12 min read

The article explains how to safely add indexes to a tens‑of‑millions‑row MySQL table, covering lock duration, disk‑space impact, write‑performance degradation, and six practical principles—selective columns, proper order, covering indexes, avoiding redundancy, short‑lock tools, and ongoing monitoring—plus pros, cons, and suitable use cases.
Large TablesMySQLPerformance Monitoring
0 likes · 10 min read

When a table exceeds a billion rows, creating a primary key clustered index can turn a routine operation into a disaster, but by using OFFLINE mode, MAXDOP, SORT_IN_TEMPDB, proper tempdb sizing, and a detailed runbook, you can safely rebuild the table with minimal impact.
Large TablesOffline IndexSQL Server
0 likes · 7 min read

This article explains MySQL partition types (RANGE, LIST, HASH, KEY), demonstrates partition pruning, details maintenance commands, presents real‑world use cases such as time‑series and massive tables, and offers best‑practice recommendations for key selection, partition count, automation, and query optimization.
Large TablesMySQLPartitioning
0 likes · 12 min read

When a tens‑million‑row order table needs a new business column, directly running ALTER TABLE can lock the table and disrupt services, so the article explores master‑slave promotion, online DDL tools, schema‑less JSON extensions, and a clever use of an existing redundant column to achieve the change safely.
DDLJSONLarge Tables
0 likes · 7 min read


ITPUBJan 11, 2026 · Databases
This article examines why adding a column to a MySQL table with tens of millions of rows can lock the table, then presents six practical solutions—including native online DDL, PT‑OSC, gh‑ost, dual‑write migration, maintenance downtime, and partition sliding‑window—detailing their procedures, trade‑offs, and suitable scenarios.
Large TablesMySQLdatabase operations
0 likes · 12 min read

When a core order table with tens of millions of rows needs a new field, a naive ALTER TABLE can lock the table and disrupt services, so this article explores master‑slave switching, online DDL tools, schema‑less extensions, and other low‑cost strategies to add the column safely.
DDLDatabase MigrationLarge Tables
0 likes · 7 min read

When adding a column to a MySQL table with tens of millions of rows, lock contention can cripple services, so this guide compares six practical solutions—including native online DDL, PT‑OSC, gh‑ost, partition sliding windows, downtime maintenance, and dual‑write migration—detailing their mechanisms, trade‑offs, use‑cases, and step‑by‑step commands to ensure minimal disruption.
Large TablesMySQLdatabase operations
0 likes · 12 min read

When a core order table with tens of millions of rows needs a new business field, directly running ALTER TABLE can lock the table and disrupt services, so this article explores master‑slave switching, online DDL tools, extension tables, JSON fields, and clever reuse of existing redundant columns to achieve the change safely and efficiently.
JSON fieldLarge TablesMySQL
0 likes · 9 min read

When a core MySQL order table reaches tens of millions of rows, adding a new column can lock the table and disrupt services, so the article walks through master‑slave switching, online DDL tools, log‑based workarounds, extension tables, JSON fields, and a final redundant‑field trick to achieve the change safely.
DDL lockJSON columnLarge Tables
0 likes · 7 min read

The article details a real‑world case of splitting a massive MySQL financial flow table, covering the initial problems, sharding strategy selection, technical challenges like multi‑datasource transactions and pagination, data migration plans, and a phased rollout to ensure system stability.
BackendDatabase MigrationLarge Tables
0 likes · 12 min read

This article explores why adding columns to massive MySQL tables can lock the database, explains the risks, and presents six practical solutions—including native online DDL, downtime maintenance, PT‑OSC, logical migration with dual writes, gh‑ost, and partition sliding windows—along with best‑practice tips for monitoring, backup, and performance.
Large TablesMySQLgh-ost
0 likes · 11 min read

By dropping a low‑selectivity composite index, adding targeted indexes on product_id‑sequence‑station_no and receive_time, and using online DDL with pt‑osc, the team reduced row scans and replication lag, then implemented batch deletions by primary‑key ranges, cutting delete time from minutes to seconds on a 100‑million‑row table.
Batch DeletionIndex OptimizationLarge Tables
0 likes · 14 min read

This article explains why massive tables become slow, identifies common bottlenecks such as disk I/O, missing indexes, deep pagination and lock contention, and provides a step‑by‑step guide covering table design, indexing, SQL tuning, sharding, caching and a real‑world case study to dramatically improve performance.
CachingLarge TablesSQL Performance
0 likes · 13 min read

This article examines how MySQL pagination performance degrades as table size grows, presents real‑world timing tests on a million‑row customer table, and offers three practical solutions—including selecting only primary keys, ID‑range filtering, and leveraging ElasticSearch—to dramatically improve query speed.
Large TablesMySQLdatabase
0 likes · 8 min read


ITPUBOct 11, 2024 · Databases
This article explains how to clean up a rapidly growing MySQL log table by batching deletions, handling unindexed type columns, and using a start‑id based pagination strategy that scales from tens of millions to billions of rows without locking the database.
Large TablesMySQLPagination
0 likes · 8 min read

This article walks through evaluating massive MySQL tables, explains why large row counts slow queries, and compares three practical solutions—table partitioning, database sharding, and hot/cold archiving—while highlighting their trade‑offs and offering guidance on selecting the right approach for a given workload.
Hot Cold ArchivingLarge TablesMySQL
0 likes · 18 min read

Facing a 50‑million‑row financial transaction table that grew 6 million rows per month, the team analyzed the problem, set clear split goals, evaluated sharding middleware, designed a custom pagination algorithm, built a hybrid data‑migration plan, and executed a three‑stage rollout to safely replace the monolithic table with multiple 10‑million‑row shards.
BackendDatabase MigrationLarge Tables
0 likes · 13 min read

This article presents a detailed investigation of a MySQL master‑slave instance suffering from nightly SLA alerts due to slow‑query latency, analyzes the root causes with pt‑query‑digest and execution plans, proposes index redesign, demonstrates backup and restore with mydumper, compares online DDL and pt‑osc, and finally shows batch‑delete techniques to reduce load on large tables.
Database PerformanceIndex OptimizationLarge Tables
0 likes · 16 min read


ITPUBDec 10, 2023 · Databases
The article investigates whether the long‑standing rule of avoiding MySQL tables larger than 20 million rows still holds on modern SSD‑backed instances by creating tables of varying sizes, measuring query latency under different InnoDB buffer pool configurations, and analyzing the impact of caching and index usage.
Buffer PoolInnoDBLarge Tables
0 likes · 11 min read

This article analyzes a large MySQL table that caused SLA alerts due to master‑slave lag, examines slow‑query logs with pt‑query‑digest, identifies index and delete inefficiencies, proposes index redesign and batch‑delete strategies, and validates the improvements with testing and DDL tools.
DDLIndex OptimizationLarge Tables
0 likes · 14 min read

Facing a MySQL transaction table exceeding 50 million rows and growing rapidly, a finance team redesigned the schema using sharding‑jdbc, chose transaction time as the shard key, tackled multi‑data‑source transactions and cross‑shard pagination, and executed a phased migration to ensure stability and performance.
Large TablesMySQLSharding-JDBC
0 likes · 11 min read

This article recounts how a finance team tackled a 50‑million‑row MySQL table by analyzing system bottlenecks, defining splitting goals, selecting sharding‑jdbc, addressing multi‑datasource transactions and pagination, designing data migration strategies, and executing a staged rollout to ensure stability and performance.
BackendDatabase MigrationLarge Tables
0 likes · 12 min read

This article explains how to assess MySQL table size, why massive tables degrade performance due to B+‑tree depth, and presents three practical solutions—table partitioning, database sharding, and hot‑cold data archiving—along with guidance on selecting the appropriate approach.
Cold ArchivingLarge TablesMySQL
0 likes · 14 min read

This article analyzes performance problems of a massive MySQL table, demonstrates how to use pt‑query‑digest and EXPLAIN to identify inefficient composite indexes, proposes index redesign, compares online DDL with pt‑osc for index rebuilding, and presents batch‑delete techniques to reduce SLA‑impacting latency.
Batch DeleteDDLIndex Optimization
0 likes · 14 min read

This article explains how MySQL’s InnoDB storage engine, page size, B+‑tree index structure, and primary‑key limits combine to make roughly 20 million rows the practical ceiling for a single table, and shows how to generate massive test data and tune server settings.
B+TreeDatabase PerformanceInnoDB
0 likes · 14 min read


21CTOSep 28, 2022 · Databases
This article walks through diagnosing slow‑query problems on a massive MySQL table, reveals index misuse, proposes dropping the old composite index, adding targeted indexes, using online DDL or pt‑osc for schema changes, and applying batch‑delete techniques to reduce latency and storage fragmentation.
Batch DeletionIndex OptimizationLarge Tables
0 likes · 14 min read

Facing SLA alerts due to slow queries on a billion‑row MySQL table, the article analyzes slow‑query logs, reveals index misuse, and demonstrates how removing a poor composite index, adding targeted indexes, employing online DDL or pt‑osc, and using batch deletions dramatically reduce query times and maintenance overhead.
Batch DeletionLarge TablesMySQL
0 likes · 16 min read


ITPUBSep 25, 2022 · Databases
Query separation stores a copy of data in a dedicated query system to accelerate large‑table reads, addressing cold‑data latency, write limitations, and scalability, with detailed guidance on when to adopt it, implementation methods (sync, async, binlog), storage options, MQ handling, and consistency considerations.
Large TablesRead Performancequery separation
0 likes · 10 min read

When trying to change a table’s primary key from int to bigint, a 500‑million‑row MySQL table required data archiving and a bulk MODIFY, but parallel DELETE statements caused lock‑wait timeouts; experiments reveal that under REPEATABLE READ the range scan locks the boundary row, leading to contention.
DELETELarge TablesLock
0 likes · 11 min read

This article explains why executing a single DELETE on a half‑billion‑row table can cause long‑running transactions, lock contention, and replication lag, and demonstrates how to split the operation into indexed batch deletes or replace it with an insert‑into‑new‑table workflow to improve safety and performance.
Batch DeletionLarge TablesSQL
0 likes · 8 min read

This article outlines a practical method for migrating MySQL tables with millions of rows by exporting data to an outfile, compressing, transferring, and loading it on the target server, while also addressing strict SQL mode errors that can arise during import.
LOAD DATALarge TablesMySQL
0 likes · 2 min read

This article describes how to improve the performance of a massive MySQL 5.6 user‑log table by redesigning schema, applying proper indexes, using partitioning, considering table sharding, and evaluating upgrade paths to compatible cloud or big‑data databases, with concrete SQL examples and cost analysis.
Large TablesMySQLPartitioning
0 likes · 16 min read


21CTOJul 9, 2020 · Databases
This article walks through diagnosing severe SLA alerts caused by slow queries on a massive MySQL table, analyzes query patterns with pt‑query‑digest, redesigns indexes, applies online DDL and pt‑osc for index changes, and demonstrates batch‑delete techniques that dramatically reduce execution time and replication lag.
Batch DeleteIndex OptimizationLarge Tables
0 likes · 15 min read

Optimizing MySQL tables with tens of millions of rows requires a systematic approach that considers data volume, table type, and performance goals, and involves design standards, business‑layer tactics, architectural strategies, and database‑specific tweaks such as proper indexing, configuration, and management practices to maintain efficiency.
Large TablesMySQLdatabase design
0 likes · 20 min read


ITPUBJun 25, 2017 · Databases
This article explains how to handle a 200‑million‑row MySQL 5.6 table by adding indexes, cleaning 99% of old data, converting it to a partitioned table, and using online DDL and auxiliary tables to maintain continuous business operations.
Large TablesMySQLPartitioning
0 likes · 7 min read

The discussion explores practical strategies for adding an index to a MySQL table containing tens of millions of rows without stopping service, covering intermediate tables, double‑write syncing, rename operations, traffic considerations, lock types, and tooling such as Percona.
Database MigrationLarge TablesMySQL
0 likes · 8 min read
![[Interview Question] How to Add an Index to a Tens‑Millions‑Row Table Without Downtime](https://wsrv.nl/?url=http%3A%2F%2Fmmbiz.qpic.cn%2Fmmbiz%2FibBPzzwQ3qZVAejXYG4PIftxa0xFibIfgicTjNtoe0Hegy7Icr4hj7VPkiaOCAOU3v5wkpv6KSiaJFjsUf0U5kFrU3Q%2F0%3Fwx_fmt%3Djpeg)