MySQL Indexes: Concept, Purpose, and Practical Examples

I noticed some frustrating things: queries on the “employees” table were taking longer than I expected. For example, filtering by last name on a table with over 100,000 rows was incredibly slow. That’s when I really started delving into MySQL indexes. Over time, I realized that understanding indexes wasn’t just academic—it involved observation, experimentation, and sometimes even hard work.

1. Indexes in MySQL

Indexes don’t change your data—they simply speed up data retrieval. However, it’s important to note that indexes must be updated every time an INSERT, UPDATE, or DELETE operation is performed. In our project, adding indexes significantly improved the performance of SELECT queries, but I noticed a slight decrease in the speed of bulk insert operations, which requires careful planning.

2. Purpose of Using Indexes

I’ve found that indexes primarily serve the following four purposes:

1. Faster Query Performance – This is obvious, but it’s also the first thing I benchmark.
2. Efficient Sorting – ORDER BY and GROUP BY operations started making sense only after adding indexes.
3. Enforcing Uniqueness – Unique indexes saved me from duplicate emails or IDs.
4. Optimizing Joins – Queries joining multiple tables suddenly stopped timing out.

3. Types of Indexes

In this project, I need to handle several different index types:

• Primary Key Index – Automatically created; guarantees uniqueness.
• Unique Index – Ensures column values ​​are unique.
• Regular Index (Non-Unique) – Speeds up searches but does not enforce uniqueness.
• Full-Text Index – Optimized for text searches.
• Composite Index – Combines multiple columns for filtering.

4. Creating Indexes in MySQL

Here’s how I approached it on our employees table.

A. Creating a Simple Index

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT
);

-- Create an index on the last_name column
CREATE INDEX idx_last_name ON employees(last_name);

At first, I wasn’t sure if indexing last_name would make a noticeable difference. Running:

SELECT * FROM employees WHERE last_name = 'Smith';

without an index was painfully slow—sometimes 0.6–0.8 seconds for a single query. After adding idx_last_name, it dropped to 0.003 seconds. That moment felt like magic. I also learned the importance of running EXPLAIN to confirm that MySQL actually used the index.

B. Creating a Unique Index

We also needed to ensure no two employees could have the same email. At first, I tried enforcing this in application logic, but under concurrent inserts, duplicates slipped through. The unique index solved it immediately:

CREATE UNIQUE INDEX idx_email ON employees(email);

The first time someone tried to insert a duplicate email, MySQL threw:

ERROR 1062: Duplicate entry '[email protected]' for key 'idx_email'

At first, I panicked, but then I realized it was MySQL protecting the data integrity. Lesson learned: always let the database enforce uniqueness when possible.

C. Creating a Composite Index

I also had a lot of queries filtering by both department_id and last_name. Initially, I had two separate indexes, but MySQL sometimes ignored them in multi-column queries. Creating a composite index fixed it:

CREATE INDEX idx_dept_name ON employees(department_id, last_name);

SELECT * FROM employees
WHERE department_id = 5 AND last_name = 'Smith';

Now MySQL efficiently fetches results using the composite index. Before this, queries took 0.5 seconds; afterwards, they were consistently under 0.01 seconds. I had to experiment with the column order too, because indexing (last_name, department_id) gave slightly worse performance in our use case.

5. How Indexes Work

Indexes are typically B-trees or hash structures:

• B-tree indexes: Ideal for range queries, sorting, and equality checks.
• Hash indexes: Extremely fast, suitable for exact match searches, but only applicable to in-memory tables.
  I immediately appreciated the advantages of an index when I first saw a query using it. EXPLAIN will display information like this:

key: idx_last_name
rows: 3

instead of a full table scan of 100,000 rows. That visual confirmation made me trust indexes more.

6. When to Use Indexes

This part took me a while to learn because I over-indexed some tables early on.

Use indexes when:

• Columns are commonly used in WHERE, JOIN, ORDER BY, or GROUP BY clauses.
• Columns ensure the uniqueness of data.

Avoid indexing when:

• Columns have low cardinality (e.g., gender or boolean flags).
• Tables are small—full table scans are sometimes faster.
• Columns are updated very often—index maintenance can slow things down.

I added six indexes to a table just in case, but the insertion speed slowed down significantly. After deleting three unnecessary indexes, the insertion speed improved by 40%.

7. Checking Index Usage

I rely heavily on EXPLAIN:

EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';

It shows whether MySQL actually used the idx_last_name index. Sometimes, if MySQL thinks a full table scan is more efficient, it will ignore the index. This used to confuse me until I learned to look at row estimates in EXPLAIN.

Indexes transformed our database from a slow crawler into a fast-responding engine. I’ve found that indexes are like seasonings in cooking: too little, and performance suffers; too much, and it slows things down. The key is to measure, experiment, and find the right balance.