Index Tuning

In the previous lesson, we learned how to analyze and optimize queries.

In this lesson, we go deeper into Index Tuning — the art of designing and optimizing indexes for maximum performance.

Poor indexing can slow down systems, while proper indexing can make queries lightning fast.

What is Index Tuning?

Index tuning is the process of:

• Choosing the right columns to index
• Selecting the correct index type
• Balancing read and write performance

The goal is to support frequent queries efficiently without unnecessary overhead.

How Indexes Work (Quick Recap)

Indexes work like a book index:

• They point directly to data locations
• Avoid full table scans
• Improve search speed dramatically

However, indexes also require maintenance.

Types of Indexes in MySQL

Choosing Columns to Index

Good candidates for indexing:

• Columns used in WHERE clauses
• Columns used in JOIN conditions
• Columns used in ORDER BY
• Columns used in GROUP BY

Avoid indexing columns with low selectivity (e.g., gender, boolean flags).

Single-Column Index

CREATE INDEX idx_salary
ON employees(salary);
  

This helps queries filtering by salary.

Composite Index

A composite index includes multiple columns.

CREATE INDEX idx_dept_salary
ON employees(department_id, salary);
  

This is useful for queries filtering by department and salary together.

Index Column Order Matters

In composite indexes, column order is critical.

Index:

(department_id, salary)
  

Works for:

• department_id
• department_id + salary

Does NOT work efficiently for:

• salary alone

Covering Index

A covering index contains all columns needed by a query.

This allows MySQL to avoid reading the table entirely.

CREATE INDEX idx_cover
ON employees(department_id, salary, name);
  

Indexes and WRITE Performance

Indexes speed up reads but slow down writes.

Every INSERT, UPDATE, or DELETE must also update indexes.

Too many indexes can:

• Increase disk usage
• Slow down data modifications

Detecting Unused Indexes

Unused indexes waste resources.

Strategies:

• Review slow query logs
• Use performance schema
• Monitor index usage statistics

Remove indexes that are never used.

Dropping an Index

DROP INDEX idx_salary ON employees;
  

Index Tuning Best Practices

• Create indexes based on real queries
• Use EXPLAIN to validate index usage
• Avoid indexing everything
• Periodically review indexes

Indexing Mistakes to Avoid

• Indexing low-cardinality columns
• Creating redundant indexes
• Ignoring composite index order
• Never cleaning up unused indexes

Real-World Index Strategy

Professional databases:

• Index for read-heavy workloads
• Minimize indexes for write-heavy systems
• Continuously tune as data grows

What’s Next?

In the next lesson, we will move into Database Design, focusing on schema planning, normalization, and scalability.