Views and indexes · SQL

Views: saved queries

A view is a named query that you can select from as if it were a table:

SQL

DROP VIEW IF EXISTS order_totals;

CREATE VIEW order_totals AS
SELECT
  o.order_id,
  c.name AS customer,
  o.status,
  ROUND(SUM(oi.quantity * p.price), 2) AS total
FROM orders o
JOIN customers c    ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p     ON p.product_id = oi.product_id
GROUP BY o.order_id;

SELECT * FROM order_totals ORDER BY total DESC LIMIT 5;

SELECT customer, COUNT(*) AS orders, ROUND(SUM(total), 2) AS spent
FROM order_totals
WHERE status <> 'cancelled'
GROUP BY customer
ORDER BY spent DESC;

That four-table join now has a short name, and the second query builds on it as if it were an ordinary table.

A view stores the query, not its results, so it always shows current data. Views are useful for:

Simplicity: hide a complicated join behind one name.
Consistency: everyone calculates "order total" the same way.
Security: in server databases, people can be given access to a view showing some columns, without access to the whole table.

PostgreSQL and others also offer materialized views, which do store their results, and must be refreshed when the data changes.

Indexes: finding rows fast

Without an index, finding the orders of customer 1 means reading every row of orders, which is called a scan. An index is a sorted lookup structure, like the index at the back of a book, that jumps straight to the matching rows: a search.

EXPLAIN QUERY PLAN shows which one the database will do:

SQL

DROP INDEX IF EXISTS idx_orders_customer;
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 1;

CREATE INDEX idx_orders_customer ON orders (customer_id);
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 1;

The first plan says SCAN orders. After the index is created, the same query becomes SEARCH orders USING INDEX idx_orders_customer. On twelve rows you can't tell the difference; on twelve million, it's the difference between seconds and milliseconds.

When to add an index

Columns you filter on often, like WHERE email = ....
Columns you join on. Foreign keys such as orders.customer_id aren't indexed automatically in SQLite, PostgreSQL or SQL Server.
Columns big tables are often sorted by.

Primary keys and UNIQUE columns get an index automatically. A unique index also enforces a rule:

SQL

CREATE UNIQUE INDEX IF NOT EXISTS idx_customers_name ON customers (name);
INSERT INTO customers (name) VALUES ('Asha Verma');

What indexes cost

Each index takes space and has to be updated on every INSERT, UPDATE and DELETE, so indexing every column slows writes down for no benefit. Index the columns your queries actually filter, join and sort on. Indexes rarely help tiny tables, or columns with only a few distinct values, such as status.