Index Strategy and Maintenance

Learning Focus

Use this lesson to understand how indexes work, when to add them, how to verify they're being used, and how to maintain them — the most impactful performance skill in MySQL.

Concept Overview

What Is an Index?

An index is a separate data structure that MySQL maintains alongside your table data. It works like the index at the back of a textbook — instead of reading every page (full table scan), you look up a keyword and jump directly to the right page.

Without an index, MySQL must examine every row in a table to answer a query. With the right index, it can find the matching rows almost instantly.

How Indexes Work (Simplified)

MySQL's default index type is a B-Tree (balanced tree). Imagine your data sorted in a tree structure:

• The root node points to ranges of values
• Branch nodes narrow down the range
• Leaf nodes contain pointers to the actual table rows

When you search WHERE customer_id = 1024, MySQL walks the tree in a few hops instead of scanning millions of rows.

Why Are Indexes Important?

Without Indexes	With Indexes
Every query scans the full table	Queries jump directly to matching rows
JOINs become painfully slow	JOINs use index lookups for each match
Performance degrades as data grows	Performance stays consistent
Simple queries take seconds	Same queries take milliseconds

The Tradeoff

Indexes are not free. Every index:

• Uses disk space (a copy of the indexed columns)
• Slows down INSERT, UPDATE, and DELETE (MySQL must update the index too)
• Must be maintained and pruned

Good indexing is a tradeoff, not a checkbox.

---

Basic Syntax & Rules

Creating Indexes

-- Single-column index
CREATE INDEX idx_orders_customer
ON orders (customer_id);

-- Composite (multi-column) index
CREATE INDEX idx_orders_customer_status_date
ON orders (customer_id, status, order_date);

-- Unique index (also enforces uniqueness)
CREATE UNIQUE INDEX idx_users_email
ON users (email);

-- Prefix index (for long text columns)
CREATE INDEX idx_products_name
ON products (name(50));

Index Types in MySQL

Index Type	Use Case	Notes
B-Tree (default)	Equality, range, sorting, prefix	Most common; works for 95% of cases
Full-Text	Text search (MATCH ... AGAINST)	For natural language search
Spatial	Geographic data (R-Tree)	For POINT, POLYGON geometry types
Hash	Exact equality (Memory engine only)	Not available for InnoDB

The Left-Prefix Rule

Composite indexes follow the left-prefix rule — MySQL can only use consecutive columns from the left side of the index.

For an index on (customer_id, status, order_date):

Query Filter	Index Used?
WHERE customer_id = 1	✅ Yes (uses first column)
WHERE customer_id = 1 AND status = 'paid'	✅ Yes (uses first two columns)
WHERE customer_id = 1 AND status = 'paid' AND order_date > '2026-01-01'	✅ Yes (uses all three)
WHERE status = 'paid'	❌ No (skips first column)
WHERE order_date > '2026-01-01'	❌ No (skips first two columns)

Column order matters.

---

Step-by-Step Examples

Example 1: Identify a Missing Index with EXPLAIN

Step 1: Run a query without an index and check the plan

EXPLAIN
SELECT order_id, customer_id, total_amount
FROM orders
WHERE customer_id = 1024
ORDER BY order_date DESC
LIMIT 20;

Expected output (no index):

type	rows	Extra
ALL	500000	Using where; Using filesort

• type = ALL means full table scan — every row is read
• rows = 500000 means MySQL estimates scanning 500K rows
• Using filesort means an extra sorting step

Step 2: Add an index

CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date);

Step 3: Run EXPLAIN again

EXPLAIN
SELECT order_id, customer_id, total_amount
FROM orders
WHERE customer_id = 1024
ORDER BY order_date DESC
LIMIT 20;

Expected output (with index):

type	rows	Extra
ref	45	Using index condition

• type = ref means index lookup — targeted access
• rows = 45 means MySQL estimates reading only 45 rows
• No Using filesort — the index already provides the sort order

Step 4: Measure real performance with EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT order_id, customer_id, total_amount
FROM orders
WHERE customer_id = 1024
ORDER BY order_date DESC
LIMIT 20;

This shows actual execution time, not just estimates.

---

Example 2: Composite Index Design

Scenario: Your dashboard shows paid orders by customer, sorted by date.

SELECT order_id, total_amount, order_date
FROM orders
WHERE customer_id = 1024
  AND status = 'paid'
ORDER BY order_date DESC
LIMIT 50;

Best index for this query:

CREATE INDEX idx_orders_cust_status_date
ON orders (customer_id, status, order_date);

Why this column order?

1. customer_id — equality filter (narrows to one customer)
2. status — equality filter (narrows to paid orders)
3. order_date — used for sorting (avoids filesort)

Key rule: Put equality columns first, then range/sort columns last.

---

Example 3: Finding and Dropping Unused Indexes

Step 1: Check which indexes exist on a table

SHOW INDEX FROM orders;

Step 2: Check index usage statistics (MySQL 8.0+)

SELECT
    index_name,
    rows_selected,
    rows_inserted,
    rows_updated,
    rows_deleted
FROM sys.schema_index_statistics
WHERE table_schema = 'app_db'
  AND table_name = 'orders';

Indexes with rows_selected = 0 over a long period are candidates for removal.

Step 3: Drop unused indexes

DROP INDEX idx_old_unused ON orders;

---

Practical Use Cases

1. Dashboard and Reporting Queries

Queries that filter by date range, status, and customer need composite indexes matching the filter pattern.

2. JOIN Optimization

Every foreign key column used in a JOIN should have an index. MySQL auto-creates indexes for FK columns, but verify older tables.

3. Search and Autocomplete

Prefix indexes (name(50)) or full-text indexes for search-by-name queries.

4. Pagination

Queries using ORDER BY ... LIMIT ... OFFSET benefit dramatically from indexes that cover the sort order.

5. Unique Business Rules

UNIQUE indexes enforce rules like "one active subscription per user" or "one review per product per customer."

---

Common Mistakes & Troubleshooting

Mistake	What Happens	How to Fix
Creating an index for every column	Slow inserts/updates, wasted disk	Only index columns used in WHERE, JOIN, ORDER BY
Wrong column order in composite index	Index is skipped by the optimizer	Follow the left-prefix rule: equality columns first
Using functions on indexed columns in WHERE	Index becomes unusable	Rewrite: WHERE order_date >= '2026-02-10' instead of WHERE DATE(order_date) = '2026-02-10'
Never running EXPLAIN	Flying blind — no proof indexes work	Run EXPLAIN on every new query pattern
Indexing low-cardinality columns alone	Poor selectivity (e.g., status with 3 values)	Combine with high-selectivity columns in a composite index
Duplicate/overlapping indexes	Extra write cost with no benefit	idx(a, b) already covers queries on (a) — drop separate idx(a)

---

Best Practices

• Measure first, then index — run EXPLAIN ANALYZE before and after to prove improvement
• Follow the slow query log — review it weekly to find queries that need indexes
• Design indexes for queries, not tables — start from your most important queries and work backward
• Consolidate overlapping indexes — one composite index often replaces multiple single-column indexes
• Drop unused indexes — check sys.schema_index_statistics monthly
• Keep composite indexes under 4–5 columns — wider indexes have diminishing returns
• Test under realistic data volume — indexes that help on 1K rows may behave differently on 1M rows

---

Hands-On Practice

Exercise 1: Add a Missing Index (Easy)

You have this slow query:

SELECT * FROM products WHERE category_id = 5 ORDER BY price ASC;

EXPLAIN shows type = ALL and Using filesort. Write the CREATE INDEX statement to fix both problems.

Exercise 2: Composite Index Design (Medium)

Your application runs this query frequently:

SELECT user_id, email, last_login
FROM users
WHERE status = 'active'
  AND country = 'ID'
ORDER BY last_login DESC
LIMIT 25;

1. Design the ideal composite index
2. Explain why you chose that column order
3. What would happen if you reversed the column order?

Exercise 3: Index Audit (Advanced)

Run SHOW INDEX FROM orders; on your busiest table and answer:

1. Are there any overlapping indexes?
2. Are there any indexes not being used? (Check sys.schema_index_statistics)
3. Can any indexes be consolidated?

---

Connection to Other Concepts

Related Concept	How It Connects
Primary & Foreign Keys	PKs automatically create indexes; FKs create indexes on child columns
EXPLAIN & Query Plans	The primary tool for validating that indexes are working
Query Refactoring	Rewriting queries to avoid function-on-column patterns that bypass indexes
Server Optimization	innodb_buffer_pool_size determines how much index data stays in memory
Constraints	UNIQUE constraints create unique indexes

What to Learn Next

• 11. Date & Time Functions — work with temporal data in your newly optimized queries

---

Visual Learning Diagram

flowchart TD
    A["Slow query detected"] --> B["Run EXPLAIN ANALYZE"]
    B --> C{"type = ALL?"}
    C -->|Yes| D["Full table scan — needs index"]
    C -->|No| E{"Using filesort?"}
    D --> F["Identify WHERE and ORDER BY columns"]
    E -->|Yes| F
    E -->|No| G["Query is already using indexes well"]
    F --> H["Design composite index"]
    H --> I["CREATE INDEX"]
    I --> J["Run EXPLAIN ANALYZE again"]
    J --> K{"Improved?"}
    K -->|Yes| L["✅ Deploy index"]
    K -->|No| M["Revise index design or rewrite query"]
    M --> F

    classDef success fill:#28a745,stroke:#fff,color:#fff
    classDef warning fill:#ffc107,stroke:#333,color:#333
    class L success
    class D,M warning

Quick Reference

-- Create single-column index
CREATE INDEX idx_name ON t(col);

-- Create composite index
CREATE INDEX idx_multi ON t(col1, col2, col3);

-- Create unique index
CREATE UNIQUE INDEX idx_unique ON t(col);

-- Drop index
DROP INDEX idx_name ON t;

-- View indexes on a table
SHOW INDEX FROM t;

-- Check query plan
EXPLAIN SELECT ... ;
EXPLAIN ANALYZE SELECT ... ;

-- Check index usage (MySQL 8.0+)
SELECT * FROM sys.schema_index_statistics
WHERE table_name = 't';

What's Next

• 11. Date & Time Functions