✏️ Explanatory Question
Level: Hard — A modern MySQL 8.0 feature that solves the "function on a column kills the index" problem elegantly.
Scenario: Users search by full name, but you store first_name and last_name separately. Your query WHERE CONCAT(first_name,' ',last_name) = 'Rumman Ansari' cannot use an index (function on columns) and does a full scan. How do you make this computed value indexable and always consistent?
A generated column is a column whose value is automatically computed from an expression over other columns — you never insert into it directly. It keeps a derived value always in sync with its source columns, and (crucially) you can index it.
| Aspect | VIRTUAL | STORED |
|---|---|---|
| Disk storage | None | Uses space |
| Computed | On read | On write |
| Write cost | Lower | Higher |
| Can be indexed | Yes | Yes |
| Add via ALTER | Instant (metadata) | Rebuilds table |
| Best for | Read-light, indexed lookups | Read-heavy, complex expressions |
Key insight: Even a VIRTUAL column can be indexed — when you add an index on it, the index physically stores the computed value, so lookups are fast without persisting the column in the row. That is why VIRTUAL + index is usually the best default.
-- Add a VIRTUAL generated column and index it
ALTER TABLE users
ADD COLUMN full_name VARCHAR(200)
AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
ADD INDEX idx_full_name (full_name);
-- Now this uses the index (no full scan, no function on raw columns)
SELECT * FROM users WHERE full_name = 'Rumman Ansari';
-- Verify
EXPLAIN SELECT * FROM users WHERE full_name = 'Rumman Ansari';CREATE TABLE orders (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
quantity INT,
price DECIMAL(10,2),
-- STORED: total is persisted, good if read very often
total DECIMAL(12,2) AS (quantity * price) STORED,
created_at DATETIME,
-- VIRTUAL: derive a date for indexing/date-only searches
order_date DATE AS (DATE(created_at)) VIRTUAL,
INDEX idx_order_date (order_date)
);
-- You never insert into generated columns; they compute automatically
INSERT INTO orders (quantity, price, created_at)
VALUES (3, 100.00, '2026-08-02 14:30:00');
-- total = 300.00, order_date = 2026-08-02 (both auto-computed)attributes->>'$.color'). Restrictions to remember: The expression must be deterministic (no NOW(), RAND(), user variables, or subqueries). You cannot write to a generated column directly. And a STORED generated column change via ALTER rebuilds the table, whereas adding a VIRTUAL one is a fast metadata-only change.
Interviewer follow-up: "When would you pick STORED over VIRTUAL?" → Choose STORED when the expression is expensive and the column is read far more than written (compute once on write, not every read), or when a downstream feature requires it to be materialized. Choose VIRTUAL for cheap expressions or when you mainly need it indexed — the index already stores the value, so persisting the column too would just waste space.