Views and Indexes

Q: Q: How can I tell if a query is using an index?

A: Use the EXPLAIN command to view the execution plan. Focus on the type column: ALL (full table scan, slowest) → index → range → ref → eq_ref → const (fastest). If type is ALL and the table has a large amount of data, an index may be needed.

Imagine you frequently borrow books from the library. There are two ways to find a book: the first is to search through every shelf every time (full table scan); the second is to check the index card catalog first and go directly to the right shelf (index query). The library's "recommended reading list" is like a view — it's not a real shelf, but a curated list of books from different shelves, making it easy to find what you want. Views and indexes in SQL work the same way.

1. Core Concepts

View — Concept

A view is a virtual table that doesn't store data but saves a SELECT query statement. Each time you access the view, the database executes the view's defined query and returns the results.

SQL

-- Create a view: employee info view
CREATE VIEW v_employee_info AS
SELECT 
    e.employee_id,
    CONCAT(e.first_name, e.last_name) AS name,
    e.salary,
    d.department_name AS department,
    d.city
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

Using a view is just like using a regular table:

SQL

-- Query the view
SELECT * FROM v_employee_info WHERE salary > 10000;

Creating Views

SQL

CREATE VIEW view_name AS
SELECT statement;

SQL

-- Create a department statistics view
CREATE VIEW v_department_stats AS
SELECT 
    d.department_id,
    d.department_name AS department,
    COUNT(e.employee_id) AS employee_count,
    AVG(e.salary) AS avg_salary,
    SUM(e.salary) AS total_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

Modifying Views

SQL

-- Method 1: CREATE OR REPLACE (recommended)
CREATE OR REPLACE VIEW v_employee_info AS
SELECT 
    e.employee_id,
    CONCAT(e.first_name, e.last_name) AS name,
    e.salary,
    e.hire_date,
    d.department_name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- Method 2: ALTER VIEW
ALTER VIEW v_employee_info AS
SELECT employee_id, first_name, last_name, salary FROM employees;

Deleting Views

SQL

DROP VIEW IF EXISTS v_employee_info;

💡 Tip: Deleting a view does not affect the underlying table data — a view is just a "shortcut" for a query.

Advantages of Views

Advantage	Description
Simplify complex queries	Encapsulate complex JOINs as views; use with just `SELECT * FROM view_name`
Data security	Expose only certain columns or rows to users, hiding sensitive data
Logical independence	Modifying view definitions doesn't affect applications using the view
Consistent logic	Ensures all users use the same query logic, avoiding repeated writing

Disadvantages of Views

Disadvantage	Description
Performance overhead	Each access requires executing the underlying query; complex views may be slow
Update limitations	Views containing JOINs, aggregate functions, or DISTINCT are usually not updatable
Debugging difficulty	Multi-layer nested views increase the difficulty of troubleshooting

💡 Tip: Views don't store data themselves — they are "query aliases." When the underlying table data changes, the view results change accordingly.

Index — Concept

An index is a data structure, similar to a book's table of contents, that helps the database quickly locate data and avoid full table scans.

SQL

-- Without index: full table scan (search row by row)
SELECT * FROM employees WHERE last_name = 'Zhang';

-- With index: direct lookup (via index)
CREATE INDEX idx_last_name ON employees(last_name);
SELECT * FROM employees WHERE last_name = 'Zhang';

Creating Indexes

SQL

-- Regular index (allows duplicate values)
CREATE INDEX idx_column ON table_name(column_name);

-- Unique index (no duplicate values allowed)
CREATE UNIQUE INDEX idx_column ON table_name(column_name);

-- Fulltext index (for full-text search)
CREATE FULLTEXT INDEX idx_column ON table_name(column_name);

-- Composite index (multi-column combination)
CREATE INDEX idx_columns ON table_name(column1, column2);

Deleting Indexes

SQL

-- MySQL syntax
DROP INDEX idx_column ON table_name;

-- Standard syntax
ALTER TABLE table_name DROP INDEX idx_column;

Index Types

Type	Keyword	Description	Use Case
Regular Index	`INDEX`	Basic index, allows duplicate values	Frequently queried columns
Unique Index	`UNIQUE INDEX`	Column values must be unique	Email, ID numbers, etc.
Fulltext Index	`FULLTEXT INDEX`	Supports full-text search	Article content, product descriptions
Composite Index	`INDEX(col1, col2)`	Multi-column combination index	Multi-condition queries

Composite Index and Leftmost Prefix Rule

Composite indexes follow the leftmost prefix rule: query conditions must match from the leftmost column of the index to use it.

SQL

-- Create a composite index
CREATE INDEX idx_name_age ON employees(last_name, first_name, salary);

-- ✅ Can use the index (matches leftmost column)
SELECT * FROM employees WHERE last_name = 'Zhang';
SELECT * FROM employees WHERE last_name = 'Zhang' AND first_name = 'Wei';

-- ❌ Cannot use the index (skips leftmost column)
SELECT * FROM employees WHERE first_name = 'Wei';
SELECT * FROM employees WHERE salary > 10000;

💡 Tip: When designing composite indexes, place the most frequently used columns in queries on the left.

Cost of Indexes

Cost	Description
Storage space	Indexes require additional disk space
Write performance	INSERT/UPDATE/DELETE need to synchronize index updates
Maintenance cost	Too many indexes reduce write speed

💡 Tip: More is not better! Only create indexes for columns that are frequently queried and have large data volumes. Small tables (a few hundred rows) usually don't need indexes.

2. Basic Syntax

View Syntax

SQL

-- Create a view
CREATE [OR REPLACE] VIEW view_name AS SELECT statement;

-- Query a view (same as a regular table)
SELECT * FROM view_name WHERE condition;

-- Delete a view
DROP VIEW [IF EXISTS] view_name;

💡 Tip: It's recommended to name views with a v_ or view_ prefix to easily distinguish them from real tables.

Index Syntax

SQL

-- Create an index
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name(column1, column2, ...);

-- Delete an index
DROP INDEX index_name ON table_name;

-- View indexes on a table
SHOW INDEX FROM table_name;

💡 Tip: Primary keys (PRIMARY KEY) and unique constraints (UNIQUE) automatically create indexes — no manual creation needed.

💡 Tip: Use EXPLAIN to check if a query uses an index: EXPLAIN SELECT * FROM employees WHERE last_name = 'Zhang';

Example: Creating and Using Views (Difficulty ⭐)

SQL

-- Create an order details view
CREATE OR REPLACE VIEW v_order_detail AS
SELECT 
    o.order_id,
    CONCAT(e.first_name, e.last_name) AS owner,
    d.department_name AS department,
    o.order_date,
    o.total_amount AS amount,
    o.status
FROM orders o
LEFT JOIN employees e ON o.employee_id = e.employee_id
LEFT JOIN departments d ON e.department_id = d.department_id;

-- Query using the view
SELECT * FROM v_order_detail WHERE status = 'completed';

▶ Try it Yourself

Output:

TEXT

order_id | owner      | department | order_date | amount   | status
---------+------------+------------+------------+----------+-----------
    1001 | WangQiang  | Sales      | 2026-05-10 | 2500.00  | completed
    1002 | WangQiang  | Sales      | 2026-05-15 | 1800.00  | completed
    1005 | ZhaoMin    | Sales      | 2026-06-10 | 4100.00  | completed

SQL

-- Use the view to aggregate order amounts by department
SELECT department, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM v_order_detail
GROUP BY department;

Query approach:

The view encapsulates the complex three-table JOIN logic
Simple queries are all that's needed — no need to rewrite the JOIN
View results automatically update as underlying data changes

Example: Creating Indexes to Optimize Query Performance (Difficulty ⭐⭐)

SQL

-- View the execution plan of the current query
EXPLAIN SELECT * FROM employees WHERE last_name = 'Zhang';

▶ Try it Yourself

Output (without index):

TEXT

+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+

type = ALL indicates a full table scan, rows = 6 indicates all rows were scanned.

SQL

-- Create an index
CREATE INDEX idx_last_name ON employees(last_name);

-- View the execution plan again
EXPLAIN SELECT * FROM employees WHERE last_name = 'Zhang';

Output (with index):

TEXT

+----+-------------+-----------+------+---------------+---------------+---------+-------+------+-------+
| id | select_type | table     | type | possible_keys | key           | key_len | ref   | rows | Extra |
+----+-------------+-----------+------+---------------+---------------+---------+-------+------+-------+
|  1 | SIMPLE      | employees | ref  | idx_last_name | idx_last_name | 152     | const |    1 |       |
+----+-------------+-----------+------+---------------+---------------+---------+-------+------+-------+

type = ref indicates the index is being used, rows = 1 indicates only 1 row was scanned.

SQL

-- Create a composite index
CREATE INDEX idx_status_date ON orders(status, order_date);

-- Query that can use the index
EXPLAIN SELECT * FROM orders WHERE status = 'completed' AND order_date > '2026-06-01';

-- View all indexes on a table
SHOW INDEX FROM employees;

Query approach:

EXPLAIN's type column: ALL (full table scan) → ref (index reference) indicates performance improvement
rows column: fewer scanned rows is better
Composite indexes must be used following the leftmost prefix rule

3. Common Application Scenarios

Scenario 1: Simplify Permission Management with Views

SQL

-- Create a view containing only non-sensitive information
CREATE OR REPLACE VIEW v_employee_public AS
SELECT 
    employee_id,
    CONCAT(first_name, last_name) AS name,
    d.department_name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- Regular users can only access this view, not salary or other sensitive info
SELECT * FROM v_employee_public;

💡 Approach: Exclude sensitive columns (like salary, email) from the view and control data access through the view.

Scenario 2: Implement Data Reports with Views

SQL

-- Create a monthly sales report view
CREATE OR REPLACE VIEW v_monthly_sales AS
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_sales,
    AVG(total_amount) AS avg_order_amount,
    COUNT(DISTINCT customer_id) AS customer_count
FROM orders
WHERE status != 'cancelled'
GROUP BY DATE_FORMAT(order_date, '%Y-%m');

-- Query the report
SELECT * FROM v_monthly_sales ORDER BY month;

Output:

TEXT

month   | order_count | total_sales | avg_order_amount | customer_count
--------+-------------+-------------+------------------+---------------
2026-05 |           2 |     4300.00 |          2150.00 |              2
2026-06 |           3 |     8250.00 |          2750.00 |              3

❓ FAQ

Q: What's the difference between a view and a temporary table? A: Views don't store data — each query executes the underlying SQL, so results are always up-to-date; temporary tables store data snapshots, offering faster queries but potentially stale data. Views are suitable for long-term query templates, while temporary tables are suitable for intermediate calculation steps.

Q: Can views modify data? A: It depends on the view definition. Views containing JOINs, aggregate functions, DISTINCT, or subqueries are usually not updatable (SELECT only). Simple single-table views (without aggregation) can be UPDATEd. In practice, views are primarily used for querying — modifying data through views is not recommended.

Q: Are more indexes always better? A: No. Indexes consume storage space and reduce INSERT/UPDATE/DELETE performance. Only create indexes for columns that are frequently queried and have high cardinality (many distinct values). Small tables usually don't need indexes.

Q: How can I tell if a query is using an index? A: Use the EXPLAIN command to view the execution plan. Focus on the type column: ALL (full table scan, slowest) → index → range → ref → eq_ref → const (fastest). If type is ALL and the table has a large amount of data, an index may be needed.

📖 Summary

Views are virtual tables that save SELECT queries, don't store data, and always stay in sync with underlying tables
Views can simplify complex queries, control data access, and standardize query logic
Views containing JOINs/aggregation/DISTINCT are usually not updatable
Indexes are the database's "table of contents" — they speed up queries but add storage and write overhead
Index types: regular index, unique index, fulltext index, composite index
Leftmost prefix rule: composite index queries must match from the leftmost column
Use EXPLAIN to analyze whether a query is using an index

📝 Exercises

Exercise 1 (⭐): Create a view v_high_salary showing employee information (name, salary, department name) for employees with salary above 10000, then use the view to query high-salary employees in the "Technology" department.

Exercise 2 (⭐⭐): Create a view v_department_report containing department name, employee count, average salary, highest salary, and lowest salary. Then use the view to find the department with the highest average salary.

Exercise 3 (⭐⭐): Create an index on the customer_id column of the orders table, and use EXPLAIN to compare query performance before and after creating the index. Then create a composite index on orders(status, order_date) and test which queries can use it.

Next Lesson

👉 18-practice-aggregate - Practice: Data Analysis: Apply aggregate functions, grouping queries, conditional expressions, and advanced functions together to improve data analysis skills through practical scenarios!

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

Views and Indexes

Views and Indexes

1. Core Concepts

View — Concept

Creating Views

Modifying Views

Deleting Views

Advantages of Views

Disadvantages of Views

Index — Concept

Creating Indexes

Deleting Indexes

Index Types

Composite Index and Leftmost Prefix Rule

Cost of Indexes

2. Basic Syntax

View Syntax

Index Syntax

Example: Creating and Using Views (Difficulty ⭐)

Example: Creating Indexes to Optimize Query Performance (Difficulty ⭐⭐)

3. Common Application Scenarios

Scenario 1: Simplify Permission Management with Views

Scenario 2: Implement Data Reports with Views

❓ FAQ

📖 Summary

📝 Exercises

Next Lesson

🙏 帮我们做得更好