⚡ Lesson 8: Indexes & Query Optimization
⏱️ Estimated time: 30 minutes | Difficulty: Intermediate
Speed Matters
As your database grows, performance becomes critical. Indexes are special lookup tables that the database search engine can use to speed up data retrieval.
What is an Index?
An index is like a book's table of contents — it helps the database find data faster without scanning every row.
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_name_age ON users(name, age); -- Composite
-- Drop an index
DROP INDEX idx_email ON users;Views (Virtual Tables)
-- Create a view (saved query)
CREATE VIEW active_users AS
SELECT name, email FROM users WHERE active = TRUE;
-- Use it like a table
SELECT * FROM active_users;
-- Drop a view
DROP VIEW active_users;Subqueries
-- Users who placed orders
SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders);
-- Users with above-average age
SELECT name, age FROM users
WHERE age > (SELECT AVG(age) FROM users);Transactions
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- Save changes
-- If something goes wrong:
ROLLBACK; -- Undo everythingPerformance Tips
- 📊 Index columns used in WHERE and JOIN
- 🚫 Avoid
SELECT *— only select needed columns - 📏 Use LIMIT for large result sets
- 🔍 Use EXPLAIN to analyze query performance
✅ Quick Quiz
❓ What does an index do?
❓ What does ROLLBACK do?
❓ What is a View?