Time Complexity: Why combining result sets is useful
O(n)
0
0
Why combining result sets is useful in MySQL - Performance Analysis
Understanding Time Complexity
When we combine result sets in SQL, we want to see how the time to get results changes as data grows.
We ask: How does combining tables affect the work the database does?
Scenario Under Consideration
Analyze the time complexity of this SQL combining two result sets.
SELECT name FROM employees
UNION
SELECT name FROM customers;
This code gets names from two tables and combines them into one list without duplicates.
Identify Repeating Operations
Look at what repeats when combining results.
- Primary operation: Scanning each table's rows to collect names.
- How many times: Once for each table, then a step to remove duplicates.
How Execution Grows With Input
As the number of rows in each table grows, the work grows too.
| Input Size (n) | Approx. Operations |
|---|---|
| 10 | About 20 rows scanned plus duplicate checks |
| 100 | About 200 rows scanned plus duplicate checks |
| 1000 | About 2000 rows scanned plus duplicate checks |
Pattern observation: The work grows roughly in direct proportion to the total rows combined.
Final Time Complexity
Time Complexity: O(n)
This means the time to combine results grows roughly in a straight line with the total number of rows.
Common Mistake
[X] Wrong: "Combining two tables always doubles the time exactly."
[OK] Correct: The time depends on total rows, but removing duplicates can add extra work, so it's not just doubling.
Interview Connect
Understanding how combining results affects time helps you explain database behavior clearly and confidently.
Self-Check
"What if we used UNION ALL instead of UNION? How would the time complexity change?"