SQL · Quick Reference

Lesson 14 — Indexes, Views & Performance Cheat Sheet

SQL

In one line: 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: fa...

Key Ideas

1What 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. Tra...

2How 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:

3EXPLAIN — Query Execution Plans. Always use EXPLAIN before and after adding indexes to verify they are being used.

4CREATE 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.

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

Code Examples

-- 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 (als...

-- 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;

-- 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 ...