Database Indexing Explained | B-Trees and Optimization

# CHAPTER 14

Indexing and Query Performance #

1. Introduction #

2. Learning Objectives #

• Understand the catastrophic mechanics of a "Full Table Scan".

• Create a B-Tree Index to massively accelerate searches.

• Create Composite Indexes for multi-column queries.

• Understand the performance trade-offs (The Cost of Indexing).

• Use the EXPLAIN keyword to diagnose slow queries.

3. The Problem: The Full Table Scan #

4. The Solution: The B-Tree Index #

If we frequently search our application by email, we must explicitly build an Index on that column!

-- DDL Command: Create an Index on the 'email' column
CREATE INDEX idx_user_email 
ON users (email);

*(Now, when a user logs in, SQL bypasses the 10-million row table, instantly checks the B-Tree index, and finds the exact row in 2 milliseconds!)*

5. Composite Indexes (Multi-Column) #

-- Create an Index combining state and status!
CREATE INDEX idx_state_status 
ON users (state, status);

-- This query is now lightning fast!
SELECT * FROM users 
WHERE state = 'NY' AND status = 'Active';

6. The Cost of Indexing (Why not index everything?) #

Every time you execute an INSERT, UPDATE, or DELETE, the database not only has to write the row into the main table, but it must mathematically recalculate and re-sort *every single B-Tree Index* attached to that table. Furthermore, Indexes consume massive amounts of physical Hard Drive space and RAM. *The Golden Rule:* Only index columns that are frequently used in WHERE, JOIN, or ORDER BY clauses.

7. Diagnosing Slow Queries (EXPLAIN) #

EXPLAIN SELECT * FROM users WHERE email = 'test@test.com';

*(If the EXPLAIN output says "Type: ALL" or "Seq Scan", the database is performing a Full Table Scan! You need an index. If it says "Index Scan", your database is fully optimized!)*

8. Auto-Indexed Columns #

9. Common Mistakes #

• Indexing Low Cardinality Columns: "Cardinality" means uniqueness. An email column has High Cardinality (every row is different). A gender column has Low Cardinality (mostly M/F). Creating a B-Tree index on a Low Cardinality column is functionally useless, because the index still has to return 50% of the entire table. The query optimizer will likely ignore the index entirely.

10. Best Practices #

• Left-to-Right Composite Rule: When using a Composite Index (e.g., (state, city)), the order of the columns matters! The index can optimize WHERE state = 'NY', or WHERE state = 'NY' AND city = 'Albany'. But it CANNOT optimize WHERE city = 'Albany' alone. A composite index only works left-to-right!

11. Exercises #

1. 1. What does the database engine perform when it searches a massive table that lacks an Index?

1. 2. What DDL command is used to physically create an index on a specific column?

12. Database Design Challenges #

13. MCQ Quiz with Answers #

14. Interview Questions #

• Q: Explain the mechanical structure of a B-Tree Index. Contrast the performance characteristics of a query executing an "Index Scan" versus a query executing a "Sequential Full Table Scan".

• Q: A developer creates a composite index on (lastname, firstname). They then run the query SELECT * FROM users WHERE first_name = 'John';. Explain why the database engine is completely unable to utilize the index for this specific query.

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #