Why computed values add flexibility in MySQL - Performance Analysis

Time Complexity: Why computed values add flexibility

O(n)

Understanding Time Complexity

We want to understand how using computed values in SQL affects the time it takes to run queries.

Specifically, how does adding calculations inside queries change the work the database does?

Scenario Under Consideration

Analyze the time complexity of the following SQL query with a computed value.

SELECT product_id, price, quantity, (price * quantity) AS total_cost
FROM sales;
    

This query calculates the total cost for each sale by multiplying price and quantity for every row.

Identify Repeating Operations

Look at what repeats as the query runs.

• Primary operation: Multiplying price by quantity for each row.
• How many times: Once for every row in the sales table.

How Execution Grows With Input

As the number of rows grows, the database does more multiplications.

Input Size (n)	Approx. Operations
10	10 multiplications
100	100 multiplications
1000	1000 multiplications

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

Final Time Complexity

Time Complexity: O(n)

This means the time to run the query grows in a straight line as the number of rows increases.

Common Mistake

[X] Wrong: "Adding a computed value doesn't affect query time much because it's just one calculation."

[OK] Correct: Even one calculation per row adds up when there are many rows, so the total work grows with the data size.

Interview Connect

Understanding how computed values affect query time shows you can think about how databases handle work as data grows, a useful skill for real projects and interviews.

Self-Check

What if we added a computed value that uses a subquery for each row? How would the time complexity change?

Practice

1. What is the main benefit of using computed values in a MySQL query?

easy

A. They create new data from existing columns without extra storage

B. They permanently change the original data in the table

C. They slow down the query execution significantly

D. They require additional tables to store results

Solution

1. Step 1: Understand computed values

   Computed values are created on the fly using existing columns without storing extra data.

2. Step 2: Compare options

   Only They create new data from existing columns without extra storage correctly states this benefit; others describe incorrect effects.

3. Final Answer:

   They create new data from existing columns without extra storage -> Option A

4. Quick Check:

   Computed values = new data without storage [OK]

Hint: Computed values don't store data, they calculate on demand [OK]

Common Mistakes:

• Thinking computed values change stored data
• Assuming computed values need extra tables
• Believing computed values always slow queries

2. Which of the following is the correct syntax to create a computed column showing total price as quantity * price in a SELECT statement?

easy

A. SELECT quantity + price AS total_price FROM sales;

B. SELECT quantity * price AS total_price FROM sales;

C. SELECT quantity price AS total_price FROM sales;

D. SELECT total_price = quantity * price FROM sales;

Solution

1. Step 1: Identify correct arithmetic operator

   Multiplication uses the * operator, so quantity * price is correct.

2. Step 2: Check SQL syntax for aliasing

   Using AS total_price correctly names the computed column.

3. Final Answer:

   SELECT quantity * price AS total_price FROM sales; -> Option B

4. Quick Check:

   Use * for multiplication and AS for alias [OK]

Hint: Use * for multiplication and AS to name computed columns [OK]

Common Mistakes:

• Using + instead of * for multiplication
• Missing AS keyword for alias
• Writing invalid syntax like quantity price

3. Given the table employees with columns first_name, last_name, and birth_year, what will this query return?

SELECT first_name, last_name, YEAR(CURDATE()) - birth_year AS age FROM employees;

medium

A. An error because YEAR() cannot be used here

B. A list of employees with their birth years only

C. A list of employees with their full names and current ages

D. A list of employees with their ages but no names

Solution

1. Step 1: Understand the computed column

   The expression YEAR(CURDATE()) - birth_year calculates current age by subtracting birth year from current year.

2. Step 2: Analyze selected columns

   The query selects first_name, last_name, and the computed age, so it returns names and ages.

3. Final Answer:

   A list of employees with their full names and current ages -> Option C

4. Quick Check:

   Computed age = current year - birth year [OK]

Hint: Subtract birth year from current year to get age [OK]

Common Mistakes:

• Thinking YEAR() function causes error
• Ignoring the computed age column
• Assuming only birth years are returned

4. Identify the error in this query that tries to compute full name:

SELECT first_name + ' ' + last_name AS full_name FROM users;

medium

A. Using + to concatenate strings causes an error in MySQL

B. Missing AS keyword for alias

C. Using single quotes around space is invalid

D. The table name users is incorrect

Solution

1. Step 1: Check string concatenation syntax in MySQL

   MySQL uses CONCAT() function, not + operator, to join strings.

2. Step 2: Verify other parts of query

   AS keyword and single quotes are correct; table name assumed valid.

3. Final Answer:

   Using + to concatenate strings causes an error in MySQL -> Option A

4. Quick Check:

   Use CONCAT() for strings, not + [OK]

Hint: Use CONCAT() to join strings in MySQL [OK]

Common Mistakes:

• Using + for string concatenation
• Confusing single quotes with double quotes
• Assuming AS is optional

5. You want to create a query that shows each product's name and its discounted price, which is 10% less than the original price stored in price. Which query correctly uses a computed value to achieve this?

hard

A. SELECT product_name, price / 0.10 AS discounted_price FROM products;

B. SELECT product_name, price * 10 AS discounted_price FROM products;

C. SELECT product_name, price + 0.10 AS discounted_price FROM products;

D. SELECT product_name, price - (price * 0.10) AS discounted_price FROM products;

Solution

1. Step 1: Calculate 10% discount correctly

   10% discount means subtracting 10% of price: price - (price * 0.10).

2. Step 2: Check other options for correctness

   SELECT product_name, price * 10 AS discounted_price FROM products; multiplies price by 10 (wrong), C adds 0.10 (wrong), D divides by 0.10 (wrong).

3. Final Answer:

   SELECT product_name, price - (price * 0.10) AS discounted_price FROM products; -> Option D

4. Quick Check:

   Discounted price = price minus 10% [OK]

Hint: Subtract 10% by price - (price * 0.10) [OK]

Common Mistakes:

• Multiplying price by 10 instead of 0.10
• Adding 0.10 instead of subtracting percentage
• Dividing price by 0.10 incorrectly