Views, Indexes & Performance · SQL & Databases

Views simplify and secure queries; indexes make them fast. Knowing when each helps (and hurts) is what separates a real SQL user.

A view is a saved query that behaves like a virtual table — it simplifies complex logic and can restrict which columns/rows a user sees. An index is a data structure that lets the database find rows fast without scanning the whole table. Both are about making queries cleaner and quicker.

Views vs indexes

A view stores a query, not data — it runs each time you select from it, giving a reusable, secure window onto the tables. An index (usually a B-tree) physically speeds up lookups on its columns, at the cost of extra storage and slower writes.

Create a view and an index

CREATE VIEW high_earners AS
    SELECT name, dept_id, salary
    FROM   employees
    WHERE  salary >= 70000;

SELECT * FROM high_earners WHERE dept_id = 10;   -- query it like a table

CREATE INDEX idx_emp_dept ON employees(dept_id);  -- speed up dept lookups/joins

Inspect the plan

EXPLAIN SELECT * FROM employees WHERE dept_id = 10;
-- shows whether an index is used or a full table scan happens

⚡ The edge

Indexes speed up reads but slow down writes and use storage — index the columns you frequently filter or join on (WHERE, JOIN, ORDER BY), not every column.
A view doesn't store data or inherently make things faster — it stores the query for reuse and security. Use EXPLAIN to see whether your query uses an index or does a full table scan.

Worked example

Which columns of employees would you index, and why?

Index columns used in WHERE filters and JOIN conditions — here dept_id (joins to departments, filters by department).
manager_id is worth indexing if you often query the hierarchy.
Avoid indexing low-value columns; every index slows inserts/updates and uses space.

Answer: Index dept_id (and manager_id) — the columns used in joins/filters; avoid indexing everything.

Worked example

Why might a query ignore an index you created?

If the predicate isn't sargable — e.g. wrapping the column in a function — the index can't be used.
If the query returns most of the table, a full scan can be cheaper than the index.
EXPLAIN reveals the optimiser's choice so you can adjust the query or index.

Answer: Non-sargable predicates or low selectivity make a full scan cheaper; EXPLAIN shows the actual plan.

⚠ Watch out

Over-indexing hurts: each index slows every INSERT/UPDATE/DELETE and consumes storage.
A view is not a performance feature by itself — it re-runs its query; a materialised view stores results (and must be refreshed).
Non-sargable predicates (function on the column, leading-wildcard LIKE '%x') prevent index use.