Views & Indexes

Views are saved queries that act as virtual tables.

Indexes

Speed up read queries. Trade-off: slower writes, more storage.

Syntax

MYSQL

1
2
3
4
5
6
7
8
9

CREATE VIEW active_users AS
SELECT id, name, email FROM users WHERE status = "active";

SELECT * FROM active_users;

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user ON orders(user_id, created_at);

SHOW INDEX FROM users;

Views and Indexes

SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- Create a view
CREATE VIEW active_users AS
SELECT id, name, email, age, city
FROM users
WHERE status = "active";

-- Query the view
SELECT * FROM active_users
WHERE city = "Lahore";

-- Create index for speed
CREATE INDEX idx_users_city ON users (city);
CREATE INDEX idx_orders_user ON orders (user_id);

-- Composite index
CREATE INDEX idx_users_city_age
ON users (city, age);

-- Show indexes
SHOW INDEX FROM users;

-- Drop view
DROP VIEW active_users;

Try It Yourself

Practice

1

Exercise

Create a view for customer order summaries.

Answer

CREATE VIEW customer_orders AS
SELECT u.name, COUNT(o.id) AS orders, SUM(o.total) AS spent
FROM users u LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

Quick Quiz

1

What do indexes improve?

Write speed

Read speed

Storage size

Data accuracy

Indexes speed up read queries at the cost of write performance.

Retry Quiz

Interview Questions

When not to use indexes?

On columns with low cardinality (few unique values), frequently updated columns, or small tables where full scans are fast.

Previous

Subqueries

Tutorial Complete

Back to MySQL