Chapter 13: Indexes & Performance

Indexes are the secret sauce that makes MySQL queries blazing fast — especially when you have thousands or millions of rows.

Today we’ll learn:

• What indexes really are (with a real-life analogy)
• How to create and drop indexes
• Types: Primary, Unique, Composite
• The magic EXPLAIN statement (how to see if MySQL is using your index)
• When to use indexes (and when NOT to — very important!)

Let’s first prepare our students table with lots of data so we can see the performance difference.

Run this to create a bigger table (copy-paste all):

Now we have ~1500 students — perfect to see the difference indexes make!

1. What are Indexes? (Real-life analogy)

Imagine your coaching class has 1500 students and their records are stored in a huge register book — pages numbered 1 to 1500.

If someone asks: “Show me all students from Mumbai”

Without an index: You have to read every single page from 1 to 1500 → very slow!

With an index: You have a separate small notebook (the index) that says:

• Mumbai students are on pages: 3, 7, 12, 15, 19, 25, 28, …
• Pune students on pages: 1, 4, 8, 11, 16, …

Now you just look at the small notebook, find the page numbers, and jump directly to those pages → super fast!

In MySQL:

• An index is a separate data structure (usually B-Tree) that stores sorted copies of one or more columns + pointers to the actual rows.
• Indexes make SELECT, WHERE, JOIN, ORDER BY, GROUP BY much faster.
• But: Indexes slow down INSERT, UPDATE, DELETE (because the index must be updated too).

2. CREATE INDEX & DROP INDEX

Syntax – Simple index on one column

Syntax – Drop index

Example – Create index on city and see speed difference

Without index (slow):

With index (fast):

You’ll feel the difference immediately on large tables!

3. Primary vs Unique vs Composite Indexes

Example – Create UNIQUE index on email

Example – Composite index (very powerful for queries using multiple columns)

This index is perfect for queries like:

4. EXPLAIN Statement – See if MySQL Uses Your Index!

EXPLAIN shows MySQL’s query execution plan — the most important tool for performance tuning!

Syntax:

Example – Without index

Look for:

• type: ALL → Full table scan (bad!)
• rows: 1500 → Scanning all rows

After creating index

Now you’ll see:

• type: ref or range → Using index (good!)
• key: idx_city → Index name
• rows: ~375 (much less than 1500)

Best possible:

• type: const or eq_ref → Super fast

5. When to Use Indexes (Golden Rules)

Use indexes when:

• Columns frequently used in WHERE clauses
• Columns used in JOIN conditions
• Columns used in ORDER BY or GROUP BY
• High selectivity (many different values, e.g., email, city, join_date)
• Primary keys & foreign keys (MySQL auto-creates indexes for them)

Do NOT index:

• Columns with very low selectivity (e.g., gender: only ‘M’/’F’ — only 2 values)
• Very small tables (< 1000 rows — no benefit)
• Columns updated very frequently (index maintenance cost)
• Columns with lots of NULL values (unless you really need it)

Best practices:

• Index foreign keys always
• Use composite indexes for queries with multiple conditions (leftmost prefix rule)
• Don’t over-index (each index takes space and slows writes)
• Use EXPLAIN before & after adding indexes

That’s it for Chapter 13! 🎉 You now understand how to make queries 10–100× faster!

Homework for today (do it right now – very important!) Run these and paste the EXPLAIN output (copy-paste the table) for each: