What are the limitations of Views in MySQL (e.g., regarding indexes or subqueries)?

Question

Accepted Answer

Views in MySQL are powerful for abstraction and simplifying complex queries, but they come with several limitations related to performance, updating, indexing, and subquery usage. Views do NOT store data physically, so every view query re-runs the underlying SELECT, which may slow performance. Views cannot have indexes because they do not store data; indexes must be created on base tables instead. Complex views with heavy JOINs, subqueries, or functions may perform poorly due to re-evaluation on each query. Many views are NOT updatable, especially if they contain JOINs, GROUP BY, DISTINCT, UNION, or subqueries. Views cannot include parameters, unlike stored procedures. Temporary tables cannot be referenced inside a view. Views with subqueries in the SELECT list may be inefficient and sometimes not allowed depending on MySQL version. If underlying tables change (rename/drop columns), the view breaks and becomes invalid. CREATE VIEW summary_view AS
SELECT user_id, COUNT(*) AS total_orders
FROM orders
GROUP BY user_id;

-- This view cannot be updated because it uses GROUP BY -- ❌ Not allowed
CREATE INDEX idx_v1 ON summary_view(user_id);
-- ERROR: Cannot create index on a view CREATE VIEW high_value_users AS
SELECT id, name,
   (SELECT SUM(order_total) FROM orders o WHERE o.user_id = u.id) AS total_spent
FROM users u;

-- Works but may be slow because subquery is re-evaluated for each row

What are the limitations of Views in MySQL (e.g., regarding indexes or subqueries)?

Limitations of Views in MySQL

What are the limitations of Views in MySQL (e.g., regarding indexes or subqueries)?

Limitations of Views in MySQL

Scenario Questions

Follow-up Questions