Indexing in MySQL

As tables grow larger, queries can become slow.
Indexes help MySQL find data faster.

In MySQL, an index is a special data structure that improves the speed of SELECT queries.

However, indexes must be used carefully because they also affect write performance.

1. What Is an Index?

An index is a data structure that stores column values in a way that allows fast searching.

Without an index:
MySQL performs a full table scan (checks every row).

With an index:
MySQL directly locates matching rows.

How Indexes Work Internally (Basic Idea)

Most MySQL indexes use a B-Tree structure.

Think of it like a sorted tree:

  • Values are organized in order.
  • Searching becomes logarithmic instead of linear.

Without index:
Time complexity ≈ O(n)

With index:
Time complexity ≈ O(log n)

That is a major improvement for large tables.

2. How Indexes Improve Performance

Indexes improve performance for:

  • WHERE clause
  • JOIN conditions
  • ORDER BY
  • GROUP BY

Example:

SELECT *

FROM Students

WHERE student_id = 101;

If student_id is indexed:
MySQL finds the row quickly.

If not:
It scans the entire table.

Indexes are especially useful when:

  • The table size is large
  • The column is frequently searched

3. Types of Indexes in MySQL

1. Primary Index (Clustered Index Concept)

When you define a PRIMARY KEY, MySQL automatically creates an index.

In InnoDB (default storage engine):

  • The primary key is the clustered index.
  • Table data is physically stored in primary key order.

Example:

CREATE TABLE Students (

    student_id INT PRIMARY KEY,

    name VARCHAR(50)

);

Only one clustered index exists per table.

2. Unique Index

Ensures all values are unique.

CREATE UNIQUE INDEX idx_email

ON Users(email);

Used for:

  • Emails
  • Usernames
  • Unique identifiers

3. Normal (Non-Unique) Index

Most common type.

CREATE INDEX idx_name

ON Students(name);

Speeds up searches but allows duplicates.

4. Composite Index

Index on multiple columns.

CREATE INDEX idx_name_city

ON Students(name, city);

Important rule: Leftmost Prefix Rule

This index works for:

  • (name)
  • (name, city)

It does not work efficiently for:

  • (city) alone

Column order matters.

5. Full-Text Index

Used for searching large text data.

CREATE FULLTEXT INDEX idx_description

ON Articles(description);

Used in:

  • Search systems
  • Blog content search

6. Prefix Index (For Large Strings)

If a column stores long strings, you can index part of it.

CREATE INDEX idx_name_prefix

ON Students(name(10));

This indexes only the first 10 characters.

Saves storage and improves performance.

4. Creating and Dropping Indexes

Create Index

CREATE INDEX index_name

ON table_name(column_name);

Create Composite Index

CREATE INDEX idx_age_city

ON Students(age, city);

Drop Index

DROP INDEX index_name

ON table_name;

5. How to Check If Index Is Used (EXPLAIN)

Use EXPLAIN before a query:

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 or range → Index used

This is important for performance tuning.

6. When Indexes Slow Things Down

Indexes improve SELECT.

But they slow down:

  • INSERT
  • UPDATE
  • DELETE

Why?

Because every time data changes:

  • The index must also be updated.

More indexes:

  • More disk space
  • Slower writes

Over-indexing harms performance.

7. When Indexes Are Not Used

Indexes may not be used when:

  1. The column has very low uniqueness (low cardinality).
    Example: gender (Male/Female)
  2. Using functions on indexed columns:
  3. WHERE YEAR(date_column) = 2024

This prevents index usage.

  1. Using wildcard at the beginning:
  2. WHERE name LIKE '%Rahul.'The index will not be used efficiently.
  1. Very small tables (full scan is faster).

8. What Is Cardinality?

Cardinality means:
Number of unique values in a column.

High cardinality:

  • email
  • user_id

Low cardinality:

  • gender
  • status (Active/Inactive)

Indexes work best on high-cardinality columns.

9. Covering Index (Advanced Beginner Concept)

A covering index means:
All required columns are inside the index.

Example:

CREATE INDEX idx_name_age

ON Students(name, age);

If the query is:

SELECT name, age

FROM Students

WHERE name = 'Rahul';

MySQL may use only the index without accessing the table data.

This improves performance further.

10. Indexing Best Practices

  1. Index columns used in WHERE.
  2. Index foreign key columns.
  3. Use composite indexes carefully.
  4. Avoid indexing low-cardinality columns.
  5. Do not create too many indexes.
  6. Use EXPLAIN to verify index usage.
  7. Prefer indexing columns used in JOIN.

Final Summary

Indexes are data structures that improve search performance.

Key points:

  • Use indexes to speed up SELECT.
  • Primary key creates a clustered index.
  • Composite index follows the leftmost prefix rule.
  • Indexes slow down write operations.
  • Use EXPLAIN to analyze queries.
  • Avoid over-indexing.

Indexes are essential for scaling large databases.