Chapter 17: Indexes

This is the chapter where we learn how to turn a slow, crawling database into a super-fast, responsive one!

Imagine your books table has 1 million rows. You run this query:

Without any help, the database has to scan every single row — like searching for a book in a library by checking every shelf one by one. That’s slow — especially as data grows.

An index is like the alphabetical index at the back of a book — it lets the database jump straight to the right page (row) without reading everything.

Today we’ll cover:

• CREATE INDEX – How to create indexes
• DROP INDEX – How to remove them
• Clustered vs. Non-clustered indexes – The two main types
• When to use indexes – And when NOT to (very important!)

Let’s use our bookshop database.

1. CREATE INDEX – Building Indexes

Syntax (basic):

Example 1 – Simple index on title

Now when you run:

The database uses the index → much faster!

Example 2 – Index on multiple columns (composite index)

This helps queries like:

Example 3 – Unique index (enforces uniqueness)

This is like adding a UNIQUE constraint but as an index — faster lookups + enforces no duplicates.

Example 4 – Index on a function (functional index – advanced)

Helps case-insensitive searches:

Pro Tip: Index names usually start with idx_ or ix_ + table + column(s).

2. DROP INDEX – Removing Indexes

Very simple — and safe (just removes the index, not the data).

Syntax:

Example:

Safe version (MySQL):

Why drop indexes?

• They take up disk space
• They slow down INSERT, UPDATE, DELETE (because index must be updated too)
• You no longer need them for old queries

3. Clustered vs. Non-clustered Indexes – The Two Main Types

Real example in our books table:

When you query by book_id → super fast (clustered) When you query by title → still fast (non-clustered), but not as fast as clustered

Important (MySQL InnoDB):

• PRIMARY KEY is always clustered
• If no PRIMARY KEY → first UNIQUE index becomes clustered
• If none → hidden row ID becomes clustered

4. When to Use Indexes – The Golden Rules (2026 Style)

Create an index when:

1. You frequently filter on a column (WHERE author_id = 3)
2. You frequently join on a column (ON b.author_id = a.author_id)
3. You frequently sort on a column (ORDER BY price DESC)
4. You have foreign keys (many databases auto-index them, but good to check)
5. Columns with high cardinality (many unique values — e.g., title, email, isbn)
6. You run range queries (price BETWEEN 200 AND 400, published_year > 2000)

Do NOT index (or remove index) when:

1. Column has low cardinality (few unique values — e.g., gender: ‘M’/’F’, status: ‘active’/’inactive’)
2. Table is very small (< 1000 rows — index may be slower)
3. You do lots of INSERT/UPDATE/DELETE on the table (indexes slow them down)
4. Column is updated very frequently (each update rebuilds index)
5. You already have too many indexes (each index takes space and slows writes)

Golden Rule for Indexes: Index columns that appear in WHERE, JOIN ON, ORDER BY, GROUP BY clauses — but only the ones you actually use!

How to check if index is used?

Look for type: ref or index (good) vs type: ALL (bad — full table scan)

Full Realistic Example Workflow