SQL Views & Indexes

Understand how SQL views simplify complex queries and how indexes accelerate data retrieval, enabling efficient database design for data science workflows.

What it is

A view is a virtual table based on the result-set of an SQL statement. It does not store data itself but acts as a saved query that can be referenced like a table. An index is a data structure (often a B-tree) associated with a table that improves the speed of data retrieval operations at the cost of additional writes and storage space. Views abstract complexity, while indexes optimize performance.

Why it matters

• Simplification: Views hide complex joins or aggregations, allowing analysts to query simple structures.
• Security: Views can restrict access to specific columns or rows without changing underlying table permissions.
• Performance: Indexes drastically reduce query time for large datasets by avoiding full table scans.
• Maintainability: Changes to underlying schema logic can be centralized in a view definition.

Syntax or steps

To create a view, use CREATE VIEW name AS SELECT .... To create an index, use CREATE INDEX name ON table(column). For unique constraints, add UNIQUE before INDEX.

Example

-- Create a sample table
CREATE TABLE sales (
    id INT PRIMARY KEY,
    product_name VARCHAR(50),
    region VARCHAR(20),
    amount DECIMAL(10, 2),
    sale_date DATE
);

-- Create an index to speed up searches by region
CREATE INDEX idx_region ON sales(region);

-- Create a view showing total sales per region
CREATE VIEW v_region_sales AS
SELECT 
    region, 
    SUM(amount) AS total_amount,
    COUNT(*) AS transaction_count
FROM sales
GROUP BY region;

-- Query the view (uses the index implicitly if filtering occurs inside, 
-- though this specific aggregate might scan all rows depending on optimizer)
SELECT * FROM v_region_sales WHERE region = 'North';

The idx_region allows the database engine to quickly locate rows where region = 'North' instead of scanning every row in the sales table. The view v_region_sales presents aggregated data as if it were a physical table.

Common mistakes

• Over-indexing: Creating too many indexes slows down INSERT, UPDATE, and DELETE operations because each index must be updated.
• Indexing low-cardinality columns: Indexing columns with few distinct values (e.g., gender) often yields little performance gain compared to high-cardinality columns (e.g., user_id).
• Ignoring view materialization: Standard views are executed every time they are queried. If the underlying query is heavy, consider materialized views (if supported) or caching results.
• Using functions on indexed columns: Queries like WHERE YEAR(sale_date) = 2023 may bypass indexes on sale_date. Use range queries (BETWEEN) instead.

When to use it

Practice

Guided Exercise: Create a view named v_high_value_customers that selects customers from a customers table where their total spend exceeds $1000. Assume a join with an orders table.

Challenge: Add an index to the orders table on the customer_id column to speed up the join used in your view. Explain why this helps.

Hint: The index allows the database to quickly find all orders belonging to a specific customer during the join operation, reducing lookup time from O(N) to O(log N).

Quick check

Q: Does creating a view physically copy the data into a new table?

A: No, a standard view stores only the query definition. Data is retrieved dynamically when the view is queried.

Summary

Views provide abstraction and reusability for complex queries, while indexes optimize read performance by structuring data for quick access. Together, they form essential tools for maintaining efficient and readable databases in data science projects.