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.