Indexes & Query Performance

#Introduction

Indexes are the single most important feature for improving MySQL query performance, and one of the top five SQL interview topics for experienced Java backend developers.

This chapter covers what indexes are, how B-Tree indexes work, clustered vs secondary (non-clustered) indexes, composite, covering, unique and full-text indexes, index selectivity, EXPLAIN, real-world performance tuning and interview questions.

#Why Do We Need Indexes?

Suppose an employee table has 10 million rows:

SELECT *
FROM employee
WHERE employee_id = 500000;

Without an index MySQL reads row 1, row 2, row 3 … row 10,000,000 — a full table scan. Very slow.

Think of a book. Without an index you read every page. With one:

That is exactly how MySQL indexes work.

#What Is an Index?

An index is a special data structure that helps MySQL locate rows quickly without scanning the entire table. Indexes speed up SELECT, JOIN, WHERE, ORDER BY and GROUP BY, and can make queries hundreds or thousands of times faster.

#B-Tree Index

MySQL's default index type is the B-Tree (InnoDB actually uses a B+Tree: all values live in the leaf pages, which are linked together for fast range scans).

Searching for 70: 50 → 80 → 70, instead of checking every row.

Real B-Tree nodes hold hundreds of keys each, so even a table with millions of rows is usually only 3–4 levels deep.

Creating and Dropping an Index

CREATE INDEX idx_employee_name ON employee (employee_name);

DROP INDEX idx_employee_name ON employee;

#Clustered Index (Primary Index)

In InnoDB, the primary key is the clustered index:

CREATE TABLE employee (
    employee_id   INT PRIMARY KEY,
    employee_name VARCHAR(100)
);

Rows are physically stored in primary-key order — the leaf pages of the clustered index are the table rows. There is only one clustered index per table.

#Secondary (Non-Clustered) Index

CREATE INDEX idx_department ON employee (department);

The index stores department + the primary key. MySQL finds the matching entries, then uses the primary key to fetch the complete row from the clustered index (a "lookup").

#Composite Index

A composite index contains multiple columns:

CREATE INDEX idx_dept_salary ON employee (department, salary);

Useful for queries like:

SELECT *
FROM employee
WHERE department = 'IT'
  AND salary > 70000;

Leftmost Prefix Rule

With the composite index (department, salary, joining_date):

An index is sorted by its first column, then the second within it, and so on — like a phone book sorted by surname then first name. You can't look up everyone named "John" without reading the whole book.

#Unique Index

Prevents duplicate values:

CREATE UNIQUE INDEX idx_email ON employee (email);

Useful for email, PAN, Aadhaar and passport numbers.

#Covering Index

SELECT department, salary
FROM employee
WHERE department = 'IT';

With the index (department, salary), MySQL can answer the query using only the index, without reading the table rows. This is a covering index — very fast. EXPLAIN shows Using index in the Extra column.

Since InnoDB secondary indexes already contain the primary key, SELECT employee_id, department, salary ... WHERE department = 'IT' is covered by the same index too.

#Full-Text Index

Designed for searching text:

CREATE FULLTEXT INDEX idx_description ON product (description);

SELECT *
FROM product
WHERE MATCH(description) AGAINST('Laptop');

Better than LIKE '%Laptop%' for large text datasets: a leading wildcard can't use a B-Tree index, so LIKE '%…%' scans every row.

#When Does MySQL Use an Index?

SELECT * FROM employee WHERE employee_id = 100;          -- primary key index
SELECT * FROM employee WHERE email = 'abc@gmail.com';    -- unique index

When Might MySQL Ignore an Index?

SELECT * FROM employee WHERE salary > 0;

If almost every row matches, a full table scan is cheaper than thousands of index lookups, so the optimizer may skip the index. The optimizer chooses based on cost estimates and statistics.

#EXPLAIN

One of the most important commands for SQL optimization:

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

id  table     type  key             rows  Extra
1   employee  ref   idx_department  200   NULL

Understanding type

From best to worst:

Aim to avoid ALL on large tables whenever possible. EXPLAIN ANALYZE (MySQL 8.0.18+) actually runs the query and reports real row counts and timings.

#Index Selectivity

Selectivity = distinct values ÷ total rows.

A highly selective index narrows the search to a few rows; a poorly selective one still leaves half the table to read.

#Real-World Examples

-- 1. Customer search
CREATE INDEX idx_customer_name ON customer (customer_name);

-- 2. Login system
CREATE UNIQUE INDEX idx_username ON users (username);

-- 3. Banking transactions — an account's history in date order
CREATE INDEX idx_account_date ON transactions (account_id, transaction_date);

-- 4. E-commerce orders
CREATE INDEX idx_customer_status ON orders (customer_id, order_status);

-- 5. Product search
CREATE FULLTEXT INDEX idx_product_desc ON product (description);

#Mini Project

Design indexes for this schema:

Tasks:

1. Create a unique index for email.
2. Create a composite index for customer ID + order date.
3. Create an index to optimize department-wise salary queries.
4. Run EXPLAIN on a query filtering by department.
5. Check whether the query uses the expected index.

#Practice Problems

Easy

1. Create an index.
2. Drop an index.
3. Create a unique index.
4. Create a composite index.
5. Run EXPLAIN on a simple query.

Medium

1. Design indexes for an orders table.
2. Build a covering index.
3. Analyze an execution plan.
4. Improve a slow WHERE query.
5. Optimize a multi-column search.

Interview level

1. Explain clustered vs secondary indexes.
2. Explain the leftmost-prefix rule.
3. Explain covering indexes with an example.
4. Explain why indexes improve search performance.
5. Identify why a query isn't using an index and propose a solution.

#Key Takeaways

• Indexes dramatically improve read performance.
• MySQL primarily uses B-Tree (B+Tree) indexes.
• In InnoDB, the primary key is the clustered index; secondary indexes store the primary key.
• Composite indexes follow the leftmost-prefix rule.
• Covering indexes can eliminate table lookups.
• EXPLAIN is essential for understanding query execution.
• Too many indexes hurt write performance.