INSERT with SELECT in MySQL - Time & Space Complexity

Time Complexity: INSERT with SELECT

O(n)

Understanding Time Complexity

When we use INSERT with SELECT in MySQL, we copy rows from one table to another. Understanding how long this takes helps us plan for bigger data.

We want to know how the time to insert grows as the number of rows selected grows.

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

INSERT INTO archive_orders (order_id, customer_id, order_date)
SELECT order_id, customer_id, order_date
FROM orders
WHERE order_date < '2023-01-01';
    

This code copies all orders before 2023 from the orders table into archive_orders.

Identify Repeating Operations

• Primary operation: Reading each row from the orders table that matches the date condition.
• How many times: Once for each matching row, then inserting that row into archive_orders.

How Execution Grows With Input

As the number of rows before 2023 grows, the work grows too because each row is read and inserted.

Input Size (n)	Approx. Operations
10	About 10 reads and 10 inserts
100	About 100 reads and 100 inserts
1000	About 1000 reads and 1000 inserts

Pattern observation: The total work grows directly with the number of rows selected.

Final Time Complexity

Time Complexity: O(n)

This means the time to complete the insert grows in a straight line with the number of rows copied.

Common Mistake

[X] Wrong: "The insert happens all at once, so time does not depend on number of rows."

[OK] Correct: Each row must be read and inserted, so more rows mean more work and more time.

Interview Connect

Knowing how insert with select scales helps you explain data migration or backup tasks clearly and confidently.

Self-Check

"What if the SELECT query had a JOIN with another large table? How would the time complexity change?"