Indexing and Query Optimization

# CHAPTER 20

Indexing and Query Optimization #

1. Introduction #

2. Learning Objectives #

• Understand the mechanical problem of a "Full Table Scan".

• Create an Index to massively accelerate WHERE 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 E-Commerce site by product_category, we must explicitly build an Index on that column!

-- DDL Command: Create an Index on the 'category' column
CREATE INDEX idx_product_category 
ON products (category);

*(Now, when a user queries WHERE category = 'Laptops', SQL bypasses the 10-million row table, instantly checks the B-Tree index, and finds the exact rows 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 table, but it must mathematically recalculate and resort every single Index attached to that table. Furthermore, Indexes take up massive amounts of physical Hard Drive space. *Rule of Thumb:* 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", it means it is doing a Full Table Scan! You need an index. If it says "Index Scan", your database is optimized!)*

8. Primary Keys and Foreign Keys are Auto-Indexed #

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 just M/F). Creating an index on a Low Cardinality column is functionally useless, because the index still has to return 50% of the entire table.

10. Best Practices #

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

11. Exercises #

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

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

12. SQL Challenges #

CREATE INDEX idx_user_status
ON orders (user_id, order_status);

13. MCQ Quiz with Answers #

14. Interview Questions #

• Q: Explain the mechanical structure of a B-Tree Index. Contrast the performance 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 unable to utilize the index for this query.

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #