Monitoring and profiling in MySQL - Time & Space Complexity

Time Complexity: Monitoring and profiling

O(n)

Understanding Time Complexity

Monitoring and profiling help us see how long database queries take and where time is spent.

We want to know how the time to run queries changes as data grows.

Scenario Under Consideration

Analyze the time complexity of the following MySQL query with profiling enabled.

SET profiling = 1;
SELECT * FROM orders WHERE customer_id = 123;
SHOW PROFILE FOR QUERY 1;
    

This code turns on profiling, runs a query to get orders for one customer, then shows the time spent on each step.

Identify Repeating Operations

Look at what repeats when the query runs.

• Primary operation: Scanning the orders table rows to find matching customer_id.
• How many times: Once per row in the orders table (depends on table size).

How Execution Grows With Input

As the number of orders grows, the time to scan grows too.

Input Size (n)	Approx. Operations
10	10 row checks
100	100 row checks
1000	1000 row checks

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

Final Time Complexity

Time Complexity: O(n)

This means the query time grows linearly as the number of rows increases.

Common Mistake

[X] Wrong: "Profiling shows constant time no matter the data size."

[OK] Correct: Profiling measures actual time, which grows with data size if no index is used.

Interview Connect

Knowing how query time grows helps you explain performance issues and improvements clearly.

Self-Check

"What if we add an index on customer_id? How would the time complexity change?"