Why Expanding a MySQL VARCHAR Column from 63 to 64 Characters Takes Much Longer

Background : A client observed that expanding a VARCHAR column from VARCHAR(20) to VARCHAR(50) was fast, but changing it from VARCHAR(50) to VARCHAR(100) took several seconds. The article reproduces this scenario to understand the cause.

Environment : MySQL 5.7.25 (Community Server) and Sysbench 1.0.17 are used for testing.

Scenario reproduction : A table sbtest1 is created with columns id (int), k (int), c (varchar(20) COLLATE utf8mb4_bin), and pad (varchar(20)). The table contains 1,000,000 rows. The SHOW CREATE TABLE output and row count are displayed.

mysql> SHOW CREATE TABLE test.sbtest1;

Problem verification : The column c is altered sequentially:

ALTER TABLE test.sbtest1 MODIFY c VARCHAR(50);   -- 0.01 sec (inplace)
ALTER TABLE test.sbtest1 MODIFY c VARCHAR(100);  -- 4.80 sec (copy)

The slow operation occurs because the maximum byte length of the column crosses the 255‑byte threshold, forcing MySQL to rebuild the table.

Further verification : Repeating the test with incremental lengths shows that the slowdown appears when changing VARCHAR(63) to VARCHAR(64) (utf8mb4) or VARCHAR(85) to VARCHAR(86) (utf8). The ALTER statements and their execution times are recorded, confirming the threshold behavior.

ALTER TABLE test.sbtest1 MODIFY c VARCHAR(63);   -- fast (inplace)
ALTER TABLE test.sbtest1 MODIFY c VARCHAR(64);   -- ~5 sec (copy)

Analysis : In utf8mb4 each character can occupy up to 4 bytes, so a column that can store up to 63 characters fits within 255 bytes (63 × 4 = 252). Expanding to 64 characters requires 256 bytes, exceeding the 1‑byte length prefix and causing MySQL to allocate a 2‑byte length field and rebuild the table, which is much slower.

Extended verification : The same tests are performed on a database using the utf8 (3‑byte) character set. The threshold appears at VARCHAR(85) → VARCHAR(86) because 85 × 3 = 255. Results are shown with ALTER TABLE … algorithm=inplace, lock=none attempts (which fail for copy‑required changes) and subsequent successful ALTER TABLE … without the algorithm hint.

Comparison table (summarized):

Length change

utf8 (MB3)

utf8mb4

20→50

online DDL (inplace)

online DDL (inplace)

50→100

online DDL (copy)

online DDL (copy)

X→Y

inplace when Y*3<256, copy otherwise

inplace when Y*4<256, copy otherwise

Conclusion : When a VARCHAR column’s maximum byte length reaches or exceeds 256 bytes, MySQL needs a 2‑byte length prefix, causing the ALTER to use the slower copy algorithm. For utf8mb4 this happens at 64 characters, for utf8 at 85 characters.

Recommendations :

For utf8 (3‑byte) columns, keep the character count ≤ 50 or ≥ 84 to stay in the inplace range.

For utf8mb4 (4‑byte) columns, keep the character count ≤ 50 or ≥ 64 to avoid copy.

Define VARCHAR lengths with these thresholds in mind to prevent costly online DDL operations in production.

All code snippets are wrapped in ... tags as required.