SQL Indexes and Views

SQL indexes make searches faster, and SQL views save a query under a name so you can reuse it like a table. Both are tools you reach for once your database grows beyond a few thousand rows.

What Is an Index?

An index is a sorted structure (usually a B-tree) that lets the database jump straight to matching rows instead of scanning the whole table, like the index at the back of a book. Primary keys are indexed automatically.

The trade-off: every INSERT, UPDATE and DELETE must also update the index, and indexes use disk space.

CREATE INDEX

Create an index on columns you often filter or join on. EXPLAIN QUERY PLAN (SQLite) shows that the index is used; MySQL and PostgreSQL use EXPLAIN.

CREATE INDEX idx_customers_country ON Customers (Country);

EXPLAIN QUERY PLAN
SELECT CustomerName FROM Customers WHERE Country = 'UK';
id | parent | notused | detail
---+--------+---------+---------------------------------------------------------------
3  | 0      | 0       | SEARCH Customers USING INDEX idx_customers_country (Country=?)
(1 row)

When to Add an Index

  • Columns used in WHERE, JOIN ON and ORDER BY on large tables
  • Foreign key columns such as Orders.CustomerID
  • Use UNIQUE INDEX to enforce uniqueness
  • Avoid indexing tiny tables or columns that are rarely searched

CREATE VIEW

A view is a stored SELECT. Querying it runs the underlying query, so it always shows current data.

CREATE VIEW OrderDetails AS
SELECT o.OrderID, c.CustomerName, p.ProductName,
       o.Quantity * p.Price AS LineTotal
FROM Orders o
JOIN Customers c ON c.CustomerID = o.CustomerID
JOIN Products p ON p.ProductID = o.ProductID;

SELECT CustomerName, SUM(LineTotal) AS spent
FROM OrderDetails
GROUP BY CustomerName
ORDER BY spent DESC
LIMIT 3;
CustomerName   | spent
---------------+------
Blue Sky Foods | 270
Harbor Goods   | 264
Golden Grains  | 240
(3 rows)

Why Use Views?

  • Hide complex joins behind a simple name
  • Give users access to some columns without exposing the whole table
  • Keep reporting logic in one place
  • Drop with DROP VIEW name

Frequently asked questions

Do indexes slow down inserts?

Yes, a little. Each index must be updated on every write, so only index columns that genuinely help your reads.

Does a view store data?

A normal view stores only the query, not the data. Materialized views (PostgreSQL, Oracle, SQL Server indexed views) do store results and must be refreshed.

How do I know if my query uses an index?

Run EXPLAIN (MySQL, PostgreSQL), EXPLAIN QUERY PLAN (SQLite) or view the execution plan in SQL Server Management Studio.