PERFORMANCE INTERMEDIATE lesson-14

Indexes, Views & Performance

🕑 32 min 📚 Lesson 14 of 15 ✅ 5 quiz questions

Indexes make queries fast; views make complex queries reusable. Together with EXPLAIN, they are the core tools of SQL performance tuning — helping you go from a 10-second query to a 10-millisecond one.

Section 1

What Is an Index?

An index is a B-tree data structure stored alongside your table. Instead of scanning every row to find a match, the database navigates the B-tree in O(log n) time. Trade-off: faster reads, slightly slower writes, extra storage.

SQL — Indexes

-- Create an index
CREATE INDEX idx_students_grade ON students(grade);
CREATE INDEX idx_orders_date ON orders(order_date);

-- Composite index (covers multiple columns)
CREATE INDEX idx_student_grade_age ON students(grade, age);

-- Unique index (also enforces uniqueness)
CREATE UNIQUE INDEX idx_email ON students(email);

-- See indexes on a table
SHOW INDEX FROM students;

-- Drop an index
DROP INDEX idx_students_grade ON students;

Section 2

How Indexes Work

The B-tree (Balanced Tree) stores index values in sorted order so the database can binary-search rather than full-scan. Key concepts:

Selectivity — an index on email (highly unique) is more useful than on a boolean column
Covered query — if all columns in SELECT and WHERE are in the index, MySQL reads the index only (no table access)
Left-prefix rule — composite index (a, b, c) helps queries filtering on a, a+b, or a+b+c, but not b alone

Section 3

EXPLAIN — Query Execution Plans

Always use EXPLAIN before and after adding indexes to verify they are being used.

SQL — EXPLAIN

-- See how MySQL executes your query
EXPLAIN SELECT * FROM students WHERE grade = 'A';
-- Look for: type=ALL (bad, full scan) vs type=ref (good, uses index)

EXPLAIN ANALYZE SELECT name FROM students WHERE grade = 'A' AND age > 16;

💡

Always EXPLAIN Slow Queries

Look for type: ALL in EXPLAIN output — that means a full table scan. Adding an index on the WHERE column usually fixes it and can drop query time from seconds to milliseconds.

Section 4

CREATE VIEW

A view is a saved SELECT query stored in the database as a named virtual table. You query it exactly like a real table — no copying of data occurs.

SQL — Views

-- A view is a saved SELECT query treated as a virtual table
CREATE VIEW top_students AS
SELECT name, grade, age
FROM students
WHERE grade IN ('A', 'A+')
ORDER BY grade ASC;

-- Query the view like a table
SELECT * FROM top_students;

-- Update a view
CREATE OR REPLACE VIEW top_students AS
SELECT name, grade FROM students WHERE grade = 'A+';

-- Drop a view
DROP VIEW top_students;

Section 5

Materialized Views in MySQL

MySQL does not support true materialized views (unlike PostgreSQL). You can simulate them with a real table that you refresh periodically:

SQL — Materialized View Workaround

-- MySQL doesn't have materialized views, but you can simulate them:
CREATE TABLE mv_grade_stats AS
SELECT grade, COUNT(*) AS total, AVG(age) AS avg_age
FROM students GROUP BY grade;

-- Refresh periodically with:
TRUNCATE TABLE mv_grade_stats;
INSERT INTO mv_grade_stats
  SELECT grade, COUNT(*), AVG(age) FROM students GROUP BY grade;

🌳

B-tree Index

Default index; excellent for range queries and sorting

Range

#️⃣

Hash Index

Exact equality matches only; O(1) lookup in memory

Exact Match

📝

Full-Text Index

Powers MATCH() AGAINST() for text search

Text Search

🗂️

Composite Index

Covers multiple columns; follow left-prefix rule

Multi-Column

⚡

Covering Index

All needed columns in index; avoids table lookup

Index-Only

🔑

Primary Index

Implicit clustered index on every PRIMARY KEY column

Clustered

🏻 Knowledge Check — Indexes & Views

1. What does an index do?

Correct! An index speeds up SELECT queries by enabling the database to find rows without scanning the entire table. The cost is storage and slower writes.

2. EXPLAIN output type='ALL' means…

Correct! type=ALL in EXPLAIN means a full table scan — every row is read. This is bad on large tables. Add an index on the WHERE column.

3. CREATE VIEW creates…

Correct! A view is a stored query — no data is copied. Querying a view re-runs the underlying SELECT against the live table data.

4. When should you add an index?

Correct! Index columns that appear in WHERE, JOIN ON, or ORDER BY clauses. Avoid over-indexing — each index slows down writes.

5. What is a downside of indexes?

Correct! Every time you write data, all relevant indexes must be updated. Heavy write tables (like log tables) can suffer from too many indexes.