Tagged articles
ALTER TABLE
15 articles · Page 1 of 1
When a new business field is required on a core order table with tens of millions of rows, the article walks through the risks of direct ALTER TABLE, evaluates master‑slave switching, online DDL tools, extension tables, JSON‑based schema‑less designs, and a clever reuse of an existing redundant column, providing practical steps and performance insights.
ALTER TABLEDatabase MigrationJSON column
0 likes · 9 min read

MySQL 8.0 (especially versions 8.0.20+) suffers a performance regression in table‑rebuilding operations like ALTER TABLE … FORCE, running up to three times slower than 5.7 due to the default innodb_doublewrite_pages setting, but the issue can be mitigated by adjusting the parameter or upgrading to 8.4+.
ALTER TABLEMySQLPerformance Regression
0 likes · 4 min read

This article analyzes why ALTER TABLE operations that extend VARCHAR columns sometimes fail in MySQL, explains the underlying Online DDL constraints, presents several real‑world problems such as default‑value handling, indexed columns, and hidden table‑rebuild quirks, and offers practical solutions for each case.
ALTER TABLEMySQLdatabase administration
0 likes · 17 min read

This article demonstrates how MySQL 8.0.29’s ALGORITHM=INSTANT enables instant column addition, deletion, and row‑size checks at any position in large tables, compares performance with previous versions, and discusses related XtraBackup backup considerations.
ALTER TABLEINSTANTMySQL
0 likes · 9 min read

This article explains why deleting rows in MySQL does not shrink the underlying table file, describes the InnoDB storage mechanics that cause this behavior, and provides practical solutions such as OPTIMIZE TABLE, ALTER TABLE, and Online DDL to reclaim space and avoid table‑locking issues.
ALTER TABLEDELETEInnoDB
0 likes · 8 min read

The article explains why MySQL’s DELETE command does not shrink the physical table file, describes InnoDB’s storage mechanics, and shows how to reclaim space using OPTIMIZE TABLE, ALTER TABLE, and Online DDL options.
ALTER TABLEDELETEDatabase Maintenance
0 likes · 7 min read

The article explains why deleting rows in MySQL does not shrink the physical table file, describes the underlying InnoDB storage mechanisms, and provides practical methods such as OPTIMIZE TABLE, ALTER TABLE, and Online DDL to reclaim space and reorganize the table.
ALTER TABLEDELETEDatabase Maintenance
0 likes · 7 min read

The article explains why deleting rows in MySQL does not shrink the .ibd file because InnoDB only marks pages as reusable, and shows how to reclaim space using OPTIMIZE TABLE, ALTER TABLE, and Online DDL options.
ALTER TABLEDELETEDatabase Maintenance
0 likes · 7 min read

This article explains why deleting rows in MySQL does not decrease the physical table file size, describes the underlying InnoDB storage mechanisms, and shows how to reclaim space using OPTIMIZE TABLE, ALTER TABLE, and Online DDL options.
ALTER TABLEDELETEInnoDB
0 likes · 7 min read

This article demonstrates how to enable MySQL's performance_schema, generate test data, run an ALTER TABLE operation, and continuously query a custom SQL statement to estimate the operation's progress and remaining time, providing practical insight into DDL monitoring.
ALTER TABLEDDL MonitoringMySQL
0 likes · 4 min read

The article explains how MySQL metadata locks, especially SHARED_UPGRADABLE and SHARED_READ_ONLY locks acquired during ALTER TABLE on tables with foreign keys, can cause long wait times under concurrent write transactions, and shows how adjusting the max_write_lock_count variable resolves the issue.
ALTER TABLEMetadata LocksMySQL
0 likes · 7 min read


ITPUBFeb 24, 2017 · Databases
This article examines the surprising behavior of Oracle when adding NOT NULL columns with default NULL values, explains how different Oracle versions handle the constraint, demonstrates test cases, and clarifies the underlying data‑dictionary mechanism that leads to unexpected query results.
ALTER TABLENOT NULLOracle
0 likes · 7 min read


ITPUBApr 29, 2016 · Databases
This guide explains why MySQL tables become fragmented, how to detect fragmentation using information_schema queries, and how to defragment tables safely with OPTIMIZE, ALTER TABLE, or MySQL startup options while noting engine‑specific behavior and best‑practice cautions.
ALTER TABLEInnoDBMyISAM
0 likes · 5 min read

This article explains how to identify table and index fragmentation in MySQL using the Data_free column, and provides two effective repair methods—altering the table engine and running OPTIMIZE TABLE—while warning about resource consumption and recommending a monthly maintenance schedule.
ALTER TABLEIndex OptimizationMySQL
0 likes · 2 min read

This article explains why ALTER TABLE statements can become blocked by MySQL’s metadata locking, describes the underlying mechanisms, outlines common scenarios that cause “Waiting for table metadata lock,” and offers practical steps to diagnose and avoid such lock contention.
ALTER TABLEMetadata LockMySQL
0 likes · 8 min read
