ALTER TABLE operations in MySQL - Time & Space Complexity

Time Complexity: ALTER TABLE operations

O(n)

Understanding Time Complexity

When we change a table's structure using ALTER TABLE, the time it takes can vary a lot.

We want to understand how the work grows as the table gets bigger.

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

ALTER TABLE employees ADD COLUMN birthdate DATE;
ALTER TABLE employees DROP COLUMN middle_name;
ALTER TABLE employees MODIFY COLUMN salary DECIMAL(10,2);
ALTER TABLE employees ADD INDEX idx_lastname (last_name);

This code changes the employees table by adding, dropping, modifying columns, and adding an index.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Scanning or rewriting the entire table data.
• How many times: Once per ALTER TABLE command that changes data storage or structure.

How Execution Grows With Input

When the table is small, changes happen quickly. As the table grows, the work grows too.

Input Size (rows)	Approx. Operations
10	About 10 data rows processed
100	About 100 data rows processed
1000	About 1000 data rows processed

Pattern observation: The work grows roughly in direct proportion to the number of rows.

Final Time Complexity

Time Complexity: O(n)

This means the time to complete ALTER TABLE grows roughly in step with the number of rows in the table.

Common Mistake

[X] Wrong: "ALTER TABLE always runs instantly no matter the table size."

[OK] Correct: Many ALTER TABLE changes require rewriting all rows, so bigger tables take longer.

Interview Connect

Understanding how table changes scale helps you explain database behavior clearly and shows you know practical impacts of schema changes.

Self-Check

"What if we only add an index without changing columns? How would the time complexity change?"