Learn how database views simplify complex queries and indexes accelerate data retrieval, enabling efficient analytics workflows.
What it is
A View is a virtual table based on the result-set of an SQL statement. It stores no data itself but acts as a saved query that can be referenced like a regular table. An Index is a separate data structure (often a B-tree) associated with a table that improves the speed of data retrieval operations. While views abstract logic, indexes optimize physical access paths.
Why it matters
- Simplification: Views hide complex joins or aggregations, allowing analysts to write simpler SELECT statements.
- Security: You can expose only specific columns or rows through a view, restricting direct access to sensitive base tables.
- Performance: Properly indexed columns drastically reduce scan times for large datasets during filtering and sorting.
- Maintainability: If underlying schema changes, updating one view definition fixes all applications relying on it.
Syntax or steps
To create a view, use CREATE VIEW name AS SELECT .... To create an index, use CREATE INDEX name ON table(column). Always ensure the column used in the WHERE clause of your frequent queries is indexed.
Example
-- Base table: sales_records
CREATE TABLE sales_records (
id INT PRIMARY KEY,
product_id INT,
sale_date DATE,
amount DECIMAL(10, 2)
);
-- Create an index to speed up date-based filtering
CREATE INDEX idx_sale_date ON sales_records(sale_date);
-- Create a view for monthly revenue analysis
CREATE VIEW monthly_revenue AS
SELECT
EXTRACT(YEAR FROM sale_date) AS year,
EXTRACT(MONTH FROM sale_date) AS month,
SUM(amount) AS total_sales
FROM sales_records
GROUP BY year, month;
-- Querying the view is simple and fast due to the index
SELECT * FROM monthly_revenue WHERE year = 2023;The idx_sale_date allows the database engine to quickly locate rows within the specified year without scanning the entire table. The monthly_revenue view encapsulates the grouping logic, so the final query remains clean.
Common mistakes
- Over-indexing: Adding too many indexes slows down INSERT, UPDATE, and DELETE operations because each index must be maintained. Only index columns frequently used in WHERE, JOIN, or ORDER BY clauses.
- Ignoring View Performance: A view containing complex subqueries may still execute slowly if the underlying tables lack proper indexes. Views do not automatically cache results unless materialized.
- Using Functions on Indexed Columns: Writing
WHERE YEAR(sale_date) = 2023often prevents the use of the index onsale_date. Instead, use range conditions likeWHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31'.
When to use it
| Feature | Use When... | Avoid When... |
|---|---|---|
| View | You need reusable logic, simplified interfaces, or row/column security. | You need real-time aggregated data stored physically (consider Materialized Views instead). |
| Index | Read-heavy workloads require fast lookups on specific columns. | Write-heavy workloads where insert/update speed is critical and reads are rare. |
Practice
Guided Exercise: Create a view named high_value_customers that selects customer names from a customers table joined with orders, filtering for orders over $500. Ensure the order_amount column in the orders table has an index.
Challenge: Modify the previous view to include the average order value per customer. Hint: Use AVG() and GROUP BY inside the view definition.
Quick check
Q: Does creating a view store a copy of the data?
A: No, a standard view stores only the query definition. Data is retrieved dynamically from the base tables when the view is queried.
Summary
Views provide logical abstraction and security by saving complex queries, while indexes provide physical optimization by speeding up data access. Combining both allows analysts to build maintainable, high-performance data pipelines without sacrificing read efficiency.
