BestHub
Sign in
Tagged articles
33 articles
Page 1 of 1
Su San Talks Tech
Su San Talks Tech
May 5, 2026 · Databases

Alibaba Interview: Key Considerations for Indexing Tens‑Millions‑Row Tables

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 TablesOnline DDLPerformance Monitoring
0 likes · 10 min read
Alibaba Interview: Key Considerations for Indexing Tens‑Millions‑Row Tables
Code Wrench
Code Wrench
Apr 12, 2026 · Databases

How to Safely Build a Primary Key Index on a 10‑Billion‑Row Table

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
How to Safely Build a Primary Key Index on a 10‑Billion‑Row Table
ITPUB
ITPUB
Jan 11, 2026 · Databases

6 Proven Strategies to Add Columns to Billion‑Row MySQL Tables Without Downtime

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.

Database operationsLarge TablesOnline DDL
0 likes · 12 min read
6 Proven Strategies to Add Columns to Billion‑Row MySQL Tables Without Downtime
dbaplus Community
dbaplus Community
Nov 17, 2025 · Databases

6 Proven Strategies to Safely Add Columns to Massive MySQL Tables

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.

Database operationsLarge TablesOnline DDL
0 likes · 12 min read
6 Proven Strategies to Safely Add Columns to Massive MySQL Tables
Architect's Tech Stack
Architect's Tech Stack
Sep 22, 2025 · Databases

How to Safely Add a Column to a Billion-Row Order Table Without Downtime

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 TablesOnline DDL
0 likes · 9 min read
How to Safely Add a Column to a Billion-Row Order Table Without Downtime
Java Tech Enthusiast
Java Tech Enthusiast
Aug 26, 2025 · Databases

6 Proven Strategies to Safely Add Columns to Billion‑Row MySQL Tables

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 TablesOnline DDLSchema Migration
0 likes · 11 min read
6 Proven Strategies to Safely Add Columns to Billion‑Row MySQL Tables
Java Tech Enthusiast
Java Tech Enthusiast
Apr 4, 2025 · Databases

MySQL Large Table Index Optimization and Batch Deletion Strategies

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
MySQL Large Table Index Optimization and Batch Deletion Strategies
Su San Talks Tech
Su San Talks Tech
Mar 30, 2025 · Databases

How to Supercharge Large Database Tables: Proven Optimization Techniques

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.

Index TuningLarge TablesSQL Performance
0 likes · 13 min read
How to Supercharge Large Database Tables: Proven Optimization Techniques
dbaplus Community
dbaplus Community
Dec 22, 2024 · Databases

Why MySQL Pagination Slows Down on Large Tables and How to Fix It

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 Tablesdatabasemysql
0 likes · 8 min read
Why MySQL Pagination Slows Down on Large Tables and How to Fix It
ITPUB
ITPUB
Oct 11, 2024 · Databases

How to Efficiently Delete Massive MySQL Logs While Preserving Critical Types

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 TablesSQL Optimizationdata deletion
0 likes · 8 min read
How to Efficiently Delete Massive MySQL Logs While Preserving Critical Types
Architect
Architect
Oct 3, 2024 · Databases

How to Tame Massive MySQL Tables: Partitioning, Sharding, and Archiving Strategies

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 TablesPerformance Optimization
0 likes · 18 min read
How to Tame Massive MySQL Tables: Partitioning, Sharding, and Archiving Strategies
Architect
Architect
Apr 17, 2024 · Databases

How We Split a 500‑Million‑Row MySQL Table: Process, Pitfalls, and Lessons

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.

BackendLarge Tablesdatabase migration
0 likes · 13 min read
How We Split a 500‑Million‑Row MySQL Table: Process, Pitfalls, and Lessons
Top Architect
Top Architect
Apr 11, 2024 · Databases

MySQL Slow Query Analysis and Index Optimization for Large Tables

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
MySQL Slow Query Analysis and Index Optimization for Large Tables
ITPUB
ITPUB
Dec 10, 2023 · Databases

Do 20‑Million‑Row MySQL Tables Still Slow Down on SSDs? Experimental Results

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.

InnoDBLarge TablesSSD
0 likes · 11 min read
Do 20‑Million‑Row MySQL Tables Still Slow Down on SSDs? Experimental Results
dbaplus Community
dbaplus Community
Jun 11, 2023 · Databases

Splitting a 500‑Million‑Row MySQL Table: Practical Sharding‑JDBC Lessons

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.

Data MigrationLarge TablesSharding-JDBC
0 likes · 11 min read
Splitting a 500‑Million‑Row MySQL Table: Practical Sharding‑JDBC Lessons
MaGe Linux Operations
MaGe Linux Operations
May 16, 2023 · Databases

How We Split a 50‑Million‑Row MySQL Table: Strategies and Lessons Learned

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.

BackendLarge Tablesdatabase migration
0 likes · 12 min read
How We Split a 50‑Million‑Row MySQL Table: Strategies and Lessons Learned
Selected Java Interview Questions
Selected Java Interview Questions
Nov 12, 2022 · Databases

MySQL Large‑Table Index Optimization and Maintenance Strategies

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
MySQL Large‑Table Index Optimization and Maintenance Strategies
21CTO
21CTO
Sep 28, 2022 · Databases

How to Optimize Large MySQL Tables: Index Tuning, Online DDL, and Batch Deletion

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
How to Optimize Large MySQL Tables: Index Tuning, Online DDL, and Batch Deletion
Java Backend Technology
Java Backend Technology
Sep 27, 2022 · Databases

Optimizing Massive MySQL Tables: Index Tuning, Online DDL & Batch Deletion

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 TablesOnline DDL
0 likes · 16 min read
Optimizing Massive MySQL Tables: Index Tuning, Online DDL & Batch Deletion
ITPUB
ITPUB
Sep 25, 2022 · Databases

Boost Large Table Queries with Query Separation: Strategies, Implementation, and Storage Choices

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
Boost Large Table Queries with Query Separation: Strategies, Implementation, and Storage Choices
dbaplus Community
dbaplus Community
Aug 29, 2022 · Databases

Why Parallel DELETEs on a MySQL Table Trigger Lock Wait Timeouts

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
Why Parallel DELETEs on a MySQL Table Trigger Lock Wait Timeouts
IT Services Circle
IT Services Circle
Jul 8, 2022 · Databases

Safely Deleting Massive Tables: Avoid Long Transactions with Batch SQL and Insert‑Instead Strategies

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 DeletionDatabase OptimizationLarge Tables
0 likes · 8 min read
Safely Deleting Massive Tables: Avoid Long Transactions with Batch SQL and Insert‑Instead Strategies
ZhiKe AI
ZhiKe AI
Jul 2, 2021 · Databases

Fast Migration of Millions of MySQL Rows: Step-by-Step Guide

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.

Data MigrationLOAD DATALarge Tables
0 likes · 2 min read
Fast Migration of Millions of MySQL Rows: Step-by-Step Guide
Architect's Tech Stack
Architect's Tech Stack
Mar 15, 2021 · Databases

Optimizing a 20‑Million‑Row MySQL Table: Design, Indexing, Partitioning, and Migration Strategies

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.

Cloud DatabasesDatabase OptimizationLarge Tables
0 likes · 16 min read
Optimizing a 20‑Million‑Row MySQL Table: Design, Indexing, Partitioning, and Migration Strategies
21CTO
21CTO
Jul 9, 2020 · Databases

How to Optimize Large MySQL Tables: Index Tuning, Online DDL, and Efficient Deletion

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
How to Optimize Large MySQL Tables: Index Tuning, Online DDL, and Efficient Deletion
dbaplus Community
dbaplus Community
Feb 16, 2020 · Databases

How to Optimize Ten‑Million‑Row MySQL Tables: Practical Guidelines

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.

Database designLarge Tablesmysql
0 likes · 20 min read
How to Optimize Ten‑Million‑Row MySQL Tables: Practical Guidelines