Indexing and Query Optimization

# CHAPTER 18

Indexing and Query Optimization #

1. Introduction #

2. Learning Objectives #

• Understand how an Index works behind the scenes.

• Create standard and composite Indexes.

• Identify the performance costs of over-indexing.

• Use the EXPLAIN keyword to diagnose slow queries.

• Optimize queries to avoid Full Table Scans.

3. What is an Index? #

• Without an Index (Full Table Scan): You flip to page 1, read it. Page 2, read it. It takes you 5 hours to find him on page 842.

• With an Index: You flip to the Glossary at the back of the book. You jump straight to 'W' -> 'Washington'. It tells you "Page 842". You jump directly there in 5 seconds.

An Index in MySQL works exactly the same way. It is a separate, highly-organized data structure (usually a B-Tree) that keeps a sorted list of specific columns and pointers to their physical hard-drive locations.

4. Creating an Index #

-- Syntax: CREATE INDEX index_name ON table_name (column_name);

CREATE INDEX idx_user_email 
ON users (email);

*Now, SELECT * FROM users WHERE email = 'bob@email.com' will execute in milliseconds, even with 10 million users!*

5. Composite Indexes #

CREATE INDEX idx_last_first_name 
ON users (last_name, first_name);

6. The EXPLAIN Keyword (The Detective Tool) #

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

MySQL will not run the query. Instead, it will output a diagnostic table. Look at the type and rows columns in the output:

• type: ALL -> Catastrophe. This means a Full Table Scan. It checked every row.

• type: ref or const -> Success! It used an index.

• rows: 1 -> Incredible! It found the data by examining exactly 1 row instead of 10 million.

7. The Dark Side of Indexing (Write Penalties) #

8. Avoiding Full Table Scans #

1. 1. Leading Wildcards: WHERE email LIKE '%@gmail.com' completely breaks the index. MySQL must check every row.

1. 2. Math on the Column: WHERE YEAR(createdat) = 2024 breaks the index on createdat. MySQL has to extract the year from every single row before comparing. Instead, write it as a range: WHERE createdat BETWEEN '2024-01-01' AND '2024-12-31'.

• Indexing Low Cardinality Columns: "Cardinality" means uniqueness. A column like email has high cardinality (every row is different) -> Great for an Index. A column like isactive has low cardinality (only 1s and 0s). An index on isactive is virtually useless and wastes space, as MySQL will likely just table-scan anyway.

1. 1. Write the SQL command to create an index named idxdepartment on the department column of the employees table.

1. 2. If you execute an EXPLAIN query and the type column returns ALL, what is MySQL doing?

12. MCQ Quiz with Answers #

13. Interview Questions #

• Q: Explain what a "Full Table Scan" is, why it degrades performance on large datasets, and how implementing an Index resolves it.

• Q: A developer has an index on the createddate column. They write a query: WHERE MONTH(createddate) = 12. The query is incredibly slow. Explain why the index failed and how to rewrite the query.

14. FAQs #

15. Summary #

16. Next Chapter Recommendation #