Why computed values add flexibility in MySQL - Why It Works This Way

Overview - Why computed values add flexibility

What is it?

Computed values are results calculated from other data in a database rather than stored directly. They can be created using expressions or formulas that combine or transform existing columns. This lets you get new information without saving extra data. Computed values update automatically when the original data changes.

Why it matters

Without computed values, you would need to store every possible piece of information, which wastes space and risks errors when data changes. Computed values solve this by calculating results on the fly, keeping data consistent and saving storage. This makes databases more flexible and easier to maintain.

Where it fits

Before learning about computed values, you should understand basic database tables and columns. After this, you can explore advanced topics like indexes on computed columns, views, and triggers that use computed values for automation.

Mental Model

Core Idea

Computed values are like formulas in a spreadsheet that automatically update when the input data changes.

Think of it like...

Imagine a calculator that always shows the sum of two numbers you enter. You never write down the sum; you just see it whenever you need it. Computed values work the same way inside a database.

┌───────────────┐       ┌───────────────┐
│  Stored Data  │──────▶│ Computed Value│
│ (e.g., price) │       │ (e.g., total) │
└───────────────┘       └───────────────┘
       ▲                        │
       │                        │
       └─────────────┬──────────┘
                     │
             ┌───────────────┐
             │ Calculation   │
             │ (price * qty) │
             └───────────────┘

Build-Up - 6 Steps

1

Foundation

Understanding Stored vs Computed Data

Concept: Distinguish between data saved directly and data calculated from other data.

In a database, some values are stored directly in columns, like a product's price. Others can be calculated, like the total cost (price times quantity). Computed values are not saved but generated when needed.

Result

You see that stored data is fixed until changed, but computed values change automatically when inputs change.

Understanding the difference between stored and computed data is key to seeing why computed values add flexibility.

2

Foundation

Basic Computed Column Syntax in MySQL

Concept: Learn how to create a computed column using MySQL expressions.

MySQL allows you to create computed columns using generated columns syntax. For example: CREATE TABLE orders ( price DECIMAL(10,2), quantity INT, total DECIMAL(10,2) AS (price * quantity) ); Here, 'total' is computed automatically.

Result

The 'total' column shows the product of price and quantity without storing it explicitly.

Knowing how to define computed columns lets you add dynamic data that updates automatically.

3

Intermediate

Benefits of Using Computed Values

🤔Before reading on: Do you think computed values save storage space or increase it? Commit to your answer.

Concept: Explore why computed values improve storage efficiency and data consistency.

Computed values avoid storing redundant data, saving disk space. They also prevent errors because the computed result always reflects current input values. For example, if price changes, total updates automatically.

Result

Databases become smaller and more reliable with computed values.

Understanding these benefits explains why computed values are widely used in real systems.

4

Intermediate

Using Computed Values in Queries

🤔Before reading on: Can you write a query that calculates a value on the fly without a computed column? Commit to your answer.

Concept: Learn how to calculate values during queries even without stored computed columns.

You can compute values directly in SELECT statements: SELECT price, quantity, price * quantity AS total FROM orders; This calculates 'total' each time you run the query without storing it.

Result

You get computed results dynamically, but repeated calculations may slow queries.

Knowing how to compute values in queries helps when you cannot or do not want to create computed columns.

5

Advanced

Indexed Computed Columns for Performance

🤔Before reading on: Do you think computed columns can be indexed to speed up queries? Commit to your answer.

Concept: Discover how indexing computed columns can improve query speed.

MySQL allows indexing of stored generated columns (computed columns stored physically). This speeds up searches and joins using computed values. Example: ALTER TABLE orders ADD COLUMN total_stored DECIMAL(10,2) AS (price * quantity) STORED; CREATE INDEX idx_total ON orders(total_stored); Queries filtering by total_stored run faster.

Result

Queries using computed values become efficient with indexes.

Understanding indexing on computed columns reveals how flexibility and performance combine in production.

6

Expert

Limitations and Surprises of Computed Values

🤔Before reading on: Do you think all expressions can be used for computed columns in MySQL? Commit to your answer.

Concept: Learn about restrictions and unexpected behaviors of computed columns.

MySQL restricts computed columns to deterministic expressions (always same output for same input). Functions like NOW() or RAND() cannot be used. Also, virtual computed columns are calculated on read, which may slow queries if overused. Understanding these limits helps avoid bugs and performance issues.

Result

You know when computed columns work well and when they don't.

Knowing these constraints prevents common mistakes and helps design better schemas.

Under the Hood

Computed values are implemented as expressions stored in the database schema. When a computed column is virtual, the database calculates its value on demand during queries by evaluating the expression using current row data. When stored, the database calculates and saves the value during insert or update operations, allowing indexing. This mechanism ensures computed values always reflect the latest data without manual updates.

Why designed this way?

This design balances flexibility and performance. Virtual columns save storage but may slow queries. Stored columns use more space but speed up access. Restricting expressions to deterministic ones ensures consistent results and reliable indexing. Alternatives like triggers were more complex and error-prone, so computed columns provide a cleaner, declarative approach.

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│   Input Data  │──────▶│ Computed Expr │──────▶│ Computed Value│
│ (price, qty)  │       │ (price*qty)   │       │ (total)       │
└───────────────┘       └───────────────┘       └───────────────┘
        │                      ▲                      │
        │                      │                      │
        │          ┌───────────┴───────────┐          │
        │          │  Virtual or Stored     │          │
        │          │  Computed Column Type  │          │
        │          └───────────────────────┘          │
        └─────────────────────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think computed columns always store data physically? Commit yes or no.

Common Belief: Computed columns always store their values in the database like normal columns.

Reality: Computed columns can be virtual (calculated on the fly) or stored (physically saved). Virtual columns do not use storage space.

Why it matters: Assuming all computed columns store data can lead to unexpected storage use or performance issues.

Quick: Can you use any function, like current date, in a computed column? Commit yes or no.

Common Belief: You can use any function, including non-deterministic ones like NOW(), in computed columns.

Reality: MySQL only allows deterministic expressions in computed columns. Functions like NOW() are not allowed.

Why it matters: Trying to use non-deterministic functions causes errors and confusion.

Quick: Do you think computed columns automatically speed up queries? Commit yes or no.

Common Belief: Computed columns always make queries faster because they pre-calculate values.

Reality: Virtual computed columns calculate values during queries, which can slow them down unless stored and indexed.

Why it matters: Misunderstanding this can cause poor performance in large databases.

Quick: Is it safe to duplicate computed values by storing them manually? Commit yes or no.

Common Belief: Manually storing computed values is just as safe and consistent as using computed columns.

Reality: Manually stored computed values can become outdated if input data changes, causing inconsistencies.

Why it matters: This can lead to wrong reports and data errors in applications.

Expert Zone

1

Stored computed columns can be indexed, but virtual ones cannot, affecting query optimization strategies.

2

Computed columns must use deterministic expressions, which excludes many useful functions, requiring workarounds like triggers.

3

Using computed columns in foreign keys or unique constraints has limitations that affect schema design.

When NOT to use

Avoid computed columns when expressions are non-deterministic or too complex for MySQL's restrictions. Instead, use triggers or application-side calculations. Also, if performance is critical and computed values change rarely, consider storing and updating them manually.

Production Patterns

In production, computed columns are used for totals, concatenated strings, or derived flags. Stored computed columns with indexes speed up reporting queries. Virtual columns are common for lightweight calculations. Complex logic often moves to views or application code for maintainability.

Connections

Spreadsheet Formulas

Same pattern of live calculation based on input cells

Understanding computed values in databases is easier when you see them as formulas in spreadsheets that update automatically.

Functional Programming

Builds on pure functions that always return the same output for the same input

Knowing that computed columns require deterministic expressions connects to the idea of pure functions in programming, ensuring consistency.

Cache Invalidation in Web Development

Opposite pattern where stored data must be refreshed manually

Computed values avoid cache invalidation problems by recalculating on demand, unlike cached data that can become stale.

Common Pitfalls

#1 Trying to use non-deterministic functions in computed columns.

Wrong approach:

CREATE TABLE sales (
sale_date DATE,
day_of_week VARCHAR(10) AS (DAYNAME(NOW()))
);

Correct approach:

CREATE TABLE sales (
sale_date DATE,
day_of_week VARCHAR(10) AS (DAYNAME(sale_date))
);

Root cause:

Misunderstanding that computed columns require deterministic expressions that depend only on row data.

#2 Assuming virtual computed columns improve query speed.

Wrong approach:

CREATE TABLE orders (
price DECIMAL(10,2),
quantity INT,
total DECIMAL(10,2) AS (price * quantity) VIRTUAL
);
-- Then expecting fast queries filtering by total

Correct approach:

CREATE TABLE orders (
price DECIMAL(10,2),
quantity INT,
total DECIMAL(10,2) AS (price * quantity) STORED
);
CREATE INDEX idx_total ON orders(total);

Root cause:

Not realizing virtual columns calculate on read, which can slow queries without indexes.

#3 Manually storing computed values and forgetting to update them.

Wrong approach:

INSERT INTO orders (price, quantity, total) VALUES (10, 2, 25); -- total is wrong

Correct approach:

Use computed columns or update total correctly:
INSERT INTO orders (price, quantity) VALUES (10, 2); -- total computed automatically

Root cause:

Not trusting the database to keep computed data consistent leads to errors.

Key Takeaways

Computed values let databases calculate data dynamically, saving storage and ensuring consistency.

They can be virtual (calculated on demand) or stored (saved physically), each with tradeoffs.

Only deterministic expressions are allowed in computed columns to guarantee reliable results.

Using indexes on stored computed columns improves query performance significantly.

Understanding computed values helps design flexible, efficient, and maintainable database schemas.

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