EXTRACT and YEAR/MONTH/DAY in MySQL - Time & Space Complexity

Time Complexity: EXTRACT and YEAR/MONTH/DAY

O(n)

Understanding Time Complexity

We want to understand how the time to get parts of a date grows as we have more data.

How does extracting year, month, or day from many rows affect performance?

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

SELECT EXTRACT(YEAR FROM order_date) AS order_year,
       EXTRACT(MONTH FROM order_date) AS order_month,
       EXTRACT(DAY FROM order_date) AS order_day
FROM orders;
    

This query extracts the year, month, and day from the order_date column for every row in the orders table.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Extracting date parts from each row's order_date.
• How many times: Once for every row in the orders table.

How Execution Grows With Input

Each row requires extracting year, month, and day once, so the work grows directly with the number of rows.

Input Size (n)	Approx. Operations
10	30 (3 parts x 10 rows)
100	300 (3 parts x 100 rows)
1000	3000 (3 parts x 1000 rows)

Pattern observation: The total work increases steadily and directly as the number of rows increases.

Final Time Complexity

Time Complexity: O(n)

This means the time to extract date parts grows in a straight line with the number of rows.

Common Mistake

[X] Wrong: "Extracting year, month, and day is a heavy operation that grows faster than the number of rows."

[OK] Correct: Extracting parts from a date is a simple, fixed-cost operation done once per row, so it grows linearly, not faster.

Interview Connect

Understanding how simple functions like EXTRACT scale helps you explain query performance clearly and confidently.

Self-Check

"What if we added a JOIN to another table before extracting date parts? How would the time complexity change?"