Indexes

As databases grow larger, queries can become slow if the database has to scan every row to find matching data.

Indexes are used to dramatically improve query performance by allowing the database to find rows quickly.

What is an Index?

An index is a data structure that helps the database engine locate rows faster.

Think of an index like:

• The index at the back of a book
• You search the index instead of reading every page

Without an index → full table scan With an index → fast lookup

How Indexes Work (Conceptual)

When you run a query with a WHERE condition, the database:

• Checks if an index exists on the column
• Uses the index to locate matching rows quickly
• Fetches only the required records

Most database systems use B-tree structures for indexing.

Example Table

Consider the employees table:

+----+----------+------------+--------+
| id | name     | department | salary |
+----+----------+------------+--------+
| 1  | Alice    | IT         | 75000  |
| 2  | Bob      | HR         | 72000  |
| 3  | Charlie  | IT         | 60000  |
| 4  | Diana    | Finance    | 80000  |
| 5  | Evan     | HR         | 58000  |
+----+----------+------------+--------+
  

Creating an Index

Create an index on the department column.

CREATE INDEX idx_department
ON employees(department);
  

This speeds up queries that filter by department.

Using Indexes in Queries

After creating an index, this query becomes faster:

SELECT *
FROM employees
WHERE department = 'IT';
  

The database uses the index instead of scanning all rows.

Unique Index

A UNIQUE INDEX ensures that no duplicate values exist in a column.

CREATE UNIQUE INDEX idx_unique_email
ON users(email);
  

This is commonly used for:

• Email addresses
• Usernames
• Unique identifiers

Composite Index

A composite index is an index on multiple columns.

CREATE INDEX idx_dept_salary
ON employees(department, salary);
  

This index helps queries that filter by:

• department
• department AND salary

Order of columns matters in composite indexes.

When Indexes Are Used

Indexes are most effective when:

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

When NOT to Use Indexes

Indexes are not always beneficial.

• Very small tables
• Columns with very few unique values
• Tables with frequent INSERT/UPDATE/DELETE operations

Indexes improve read performance but slow down writes.

Checking Index Usage

You can see how MySQL executes a query using:

EXPLAIN SELECT *
FROM employees
WHERE department = 'IT';
  

EXPLAIN shows whether an index is used or not.

Indexes vs Full Table Scan

Common Beginner Mistakes

• Creating too many indexes
• Indexing every column blindly
• Ignoring composite index order
• Not using EXPLAIN

Best Practices

• Index columns used in WHERE and JOIN
• Keep indexes minimal and meaningful
• Monitor performance regularly

What’s Next?

In the next lesson, we will explore Normalization, which focuses on designing efficient and clean database structures.