What is the difference between a simple View and a complex View?

0-2 years experience

1. 1Suppose you need to expose only the email column from the users table to a reporting tool. How would you create a view, and would you consider it a simple or complex view?
2. 2If you add a new column to the underlying table, what happens to an existing simple view that selects *? How would you verify it still works?
3. 3You have a view that selects first_name and last_name from employees. If you query the view with a WHERE clause on salary, what does MySQL do under the hood?

2-5 years experience

1. 1Your team built a view that joins orders and customers and aggregates total spend per customer. It’s running slowly. Walk me through how you’d diagnose whether it’s a simple vs complex view issue.
2. 2We tried to add a GROUP BY to an existing simple view and got an error about non‑deterministic columns. Why did that happen, and how would you refactor the view?
3. 3During a deployment, a change to the products table broke a view that was used by several services. How would you determine if the view was simple or complex and decide the fix path?

5-8 years experience

1. 1Design a strategy for replacing a set of complex reporting views with a more maintainable solution that scales to billions of rows. What trade‑offs do you consider between materialized views, indexed views, and query rewriting?
2. 2Our analytics pipeline relies on a complex view that joins ten tables and uses window functions. Under heavy load, the view causes lock contention. How would you redesign it, and what role does view complexity play in your decision?
3. 3Explain how MySQL’s optimizer treats simple vs complex views differently, and how you’d use EXPLAIN to verify that a complex view isn’t causing a full table scan.

8+ years experience

1. 1We are migrating a legacy monolith to microservices, and many services depend on a library of complex MySQL views. How would you approach refactoring or deprecating these views while ensuring backward compatibility?
2. 2At a large e‑commerce company, you need to decide whether to keep critical reporting logic in complex views or move it to an ELT pipeline. Discuss the long‑term architectural implications, including schema evolution, testing, and data latency.
3. 3Your organization wants to enforce a policy that all new views must be simple unless a documented performance case exists. How would you implement governance, tooling, and review processes to enforce this at scale?