Home›SQL›MySQL – Views

MySQL – Views

November 17, 20245 min readUpdated 8/25/2026

A view is a stored SELECT that behaves like a table. It stores no data — the query runs when you read the view — so it is a naming and access-control tool rather than a performance one.

Creating one

CREATE VIEW order_summary AS
SELECT o.id, o.customer_name, o.status, COUNT(i.id) AS items, ROUND(SUM(i.line_total), 2) AS goods_total
FROM   customer_order o LEFT JOIN order_item i ON i.order_id = o.id
GROUP  BY o.id, o.customer_name, o.status;
SELECT * FROM order_summary WHERE status = 'COMPLETED' ORDER BY id LIMIT 4;
+----+---------------+-----------+-------+-------------+
| id | customer_name | status    | items | goods_total |
+----+---------------+-----------+-------+-------------+
|  1 | Demo Customer | COMPLETED |     2 |       22.97 |
|  2 | Alex Rivera   | COMPLETED |     1 |       16.99 |
|  3 | Demo Customer | COMPLETED |     2 |       25.98 |
|  4 | Sam Chen      | COMPLETED |     1 |       25.98 |
+----+---------------+-----------+-------+-------------+

The join and the aggregation are written once and used by name after that. CREATE OR REPLACE VIEW redefines an existing one; DROP VIEW removes it. Note that the column names come from the SELECT, so aliasing every expression is worth doing — an unaliased COUNT(i.id) becomes a column literally called COUNT(i.id).

MERGE or TEMPTABLE — and why it matters

MySQL processes a view one of two ways, and the difference is the whole performance story.

MERGE rewrites your query to use the underlying tables directly, so your WHERE reaches them and their indexes apply:

CREATE VIEW active_menu AS
SELECT id, name, type, active FROM product WHERE active = TRUE AND deleted = FALSE WITH CHECK OPTION;

EXPLAIN SELECT id, name FROM active_menu WHERE id = 5;
+----+-------------+---------+------------+-------+-----------------------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys               | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+-----------------------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | product | NULL       | const | PRIMARY,idx_product_deleted | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+-----------------------------+---------+---------+-------+------+----------+-------+

The plan names product, not a derived table, and uses the primary key. The view cost nothing.

TEMPTABLE is the other path: MySQL materialises the view's whole result into a temporary table and then applies your query to that. It is forced by anything MySQL cannot merge through — GROUP BY, DISTINCT, aggregates, UNION, a window function, LIMIT.

order_summary above has a GROUP BY, so it is a TEMPTABLE view. That means SELECT * FROM order_summary WHERE id = 5 aggregates every order first and then keeps one row — the filter cannot reach the base table, and no index helps. On 18 orders that is invisible; on 400,000 it is the difference between instant and unusable.

This is the single most important thing to know about views: a view that aggregates does not filter cheaply. Check with EXPLAIN rather than assuming.

Updatable views

A simple enough view can be written through:

UPDATE active_menu SET name = 'Pepperoni Pizza ' WHERE id = 1;

A view with aggregation cannot be:

-- ERROR 1288 (HY000): The target table order_summary of the UPDATE is not updatable
UPDATE order_summary SET status = 'PAID' WHERE id = 1;

The rule is roughly: one base table, no GROUP BY, no DISTINCT, no aggregates, no UNION, and every selected column a plain column rather than an expression.

WITH CHECK OPTION

Without it, an update through a view can push a row out of that view — you change a value, the row stops matching the view's WHERE, and it vanishes. That is almost never intended:

-- ERROR 1369 (HY000): CHECK OPTION failed 'active_menu'
UPDATE active_menu SET active = FALSE WHERE id = 1;

WITH CHECK OPTION refuses any write whose result would not be visible through the view. If a view is writable at all, it usually wants this.

What views are genuinely good for

  • Naming a complicated query so it is written and reviewed once.
  • A permission boundary. Grant SELECT on a view instead of the table and a reporting account sees the columns you chose and no others — no salary column, no card details. See users and privileges.
  • Enforcing a filter mechanically. The pizza schema soft-deletes with a deleted flag, and every query has to remember it. A view that bakes in WHERE deleted = FALSE is the database-side answer to that — the application uses Hibernate's @SQLRestriction for the same reason. See DELETE.
  • A stable interface over a schema you intend to change.

What they are not

MySQL has no materialised views. Postgres and Oracle can store a view's result and refresh it; MySQL cannot. A view is always the query, run again.

The usual substitutes, in order of how often they are the right answer:

  1. A real summary table, refreshed by a scheduled job or on write. Explicit, indexable, and you control the staleness.
  2. A scheduled event that rebuilds it inside the database.
  3. Caching the result in the application.

Two more limits worth knowing: a view cannot have an index of its own — only the base tables can — and nesting views is where performance goes to die. A view over a view over a view materialises repeatedly and the plan becomes very hard to read. One level is usually plenty.

Seeing what is there

SHOW FULL TABLES WHERE Table_type = 'VIEW';
SHOW CREATE VIEW order_summary;

Views also have a definer and a security model: SQL SECURITY DEFINER (the default) runs the view with its creator's privileges, which is what makes the permission-boundary use work; SQL SECURITY INVOKER runs it with the caller's. Be deliberate about which — a DEFINER view created by root grants its caller root's reach over the tables it names.

What to remember

  • A view stores no data; the query runs each time.
  • MERGE keeps your filters on the base tables; GROUP BY forces TEMPTABLE and loses that.
  • Simple views are updatable — add WITH CHECK OPTION.
  • No materialised views in MySQL. Use a summary table.
  • DEFINER versus INVOKER is a security decision, not a detail.