Advanced Indexing & Performance Optimization in MySQL

In the previous article, you learned the basics of indexing:

  • What an index is
  • Types of indexes
  • How to create and drop indexes

Now we will go one level deeper and understand how indexing affects performance optimization.

All examples apply to MySQL.

This article is still beginner-friendly, but it focuses on performance understanding.

1. How MySQL Stores Indexes (Basic Idea)

Most MySQL indexes use a B-Tree structure.

You do not need deep internal knowledge, but understand this:

  • Data is stored in sorted order.
  • Searching becomes much faster.
  • Instead of checking every row, MySQL narrows down quickly.

Without index:

  • MySQL scans the entire table.

With index:

  • MySQL searches using a tree structure.

This is why indexes dramatically improve performance for large tables.

2. Clustered vs Non-Clustered Index (InnoDB Concept)

MySQL uses InnoDB as the default storage engine.

Clustered Index

  • The PRIMARY KEY is the clustered index.
  • Table data is physically stored in primary key order.
  • Only one clustered index per table.

Example:

CREATE TABLE Students (

    student_id INT PRIMARY KEY,

    name VARCHAR(50)

);

Here, table rows are stored in order of student_id.

Non-Clustered Index

  • Any other index apart from the primary key.
  • Stores indexed column values and a pointer to the actual row.

You can have multiple non-clustered indexes.

3. What Is Cardinality?

Cardinality means:

Number of unique values in a column.

High cardinality:

  • email
  • user_id
  • order_id

Low cardinality:

  • gender
  • status (Active/Inactive)

Indexes work best on high-cardinality columns.

Indexing low-cardinality columns often gives little benefit.

4. Covering Index

A covering index means:

All columns required by a query are present inside the index.

Example:

CREATE INDEX idx_name_age

ON Students(name, age);

Query:

SELECT name, age

FROM Students

WHERE name = 'Rahul';

MySQL can use only the index without reading full table rows.

This reduces disk access and improves performance.

5. When Indexes Are NOT Used

Indexes are not always used.

Common situations:

1. Using Functions on an Indexed Column

SELECT *

FROM Students

WHERE YEAR(dob) = 2000;

This prevents index usage.

Better:

WHERE dob BETWEEN '2000-01-01' AND '2000-12-31';

2. Leading Wildcard in LIKE

WHERE name LIKE '%Rahul';

The index will not be used efficiently.

But this works:

WHERE name LIKE 'Rahul%';

3. Very Small Tables

For small tables, a full scan may be faster.

6. Using EXPLAIN to Analyze Queries

Before optimizing, use EXPLAIN.

EXPLAIN SELECT *

FROM Students

WHERE age = 20;

EXPLAIN shows:

  • Whether the index is used
  • Type of scan
  • Estimated rows examined

If you see:
type = ALL → Full table scan
type = ref / range → Index used

This helps diagnose performance problems.

7. Composite Index and Leftmost Prefix Rule

If you create:

CREATE INDEX idx_name_city

ON Students(name, city);

It works for:

  • WHERE name = 'Rahul'
  • WHERE name = 'Rahul' AND city = 'Delhi'

But not efficiently for:

  • WHERE city = 'Delhi'

Because MySQL follows the Leftmost Prefix Rule.

Column order matters in composite indexes.

8. Over-Indexing Problem

Too many indexes cause:

  • Slower INSERT
  • Slower UPDATE
  • Slower DELETE
  • More storage usage

Every time data changes:

  • The index must also be updated.

So balance is important.

9. Basic Performance Optimization Tips

  1. Index columns used frequently in WHERE.
  2. Index columns used in JOIN conditions.
  3. Avoid indexing columns with low uniqueness.
  4. Avoid unnecessary composite indexes.
  5. Use EXPLAIN before and after adding an index.
  6. Remove unused indexes.

Performance tuning should be align="center" style="text-align:center">