Can you explain the concept of a materialized view?

Short answer: A materialized view is a database object that stores the result of a query physically, similar to an indexed view but more generalized.

Explain a bit more

Unlike regular views, which generate their result dynamically when queried, materialized views store the result set and can be periodically refreshed. Advantages: Faster query performance, especially for complex queries or aggregations, since the data is precomputed and stored. Refresh: The data in a materialized view may become outdated over time, so it needs to be refreshed periodically, either manually or automatically, depending on the DBMS.

Example code

CREATE MATERIALIZED VIEW sales_summary AS SELECT product_id, SUM(sales) AS total_sales FROM sales GROUP BY product_id; - Refresh the materialized view when needed: REFRESH MATERIALIZED VIEW sales_summary; Materialized views are supported in systems like PostgreSQL and Oracle, but MySQL does not have a built-in materialized view feature. You can simulate one using tables and scheduled jobs.

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.