Database indexes explained: types, composite indexes and when they help

An index is the single biggest reason one query takes a few milliseconds and another takes several seconds on the same table. It is also a favourite interview subject: "what is an index, what types are there, and when does a database not use it?" This post explains how indexes work using the idea of a book's index, then demonstrates each rule on a table of 100,000 rows in MySQL 8.4 with real EXPLAIN output. You will see the plan before and after, the leftmost-prefix rule for composite indexes, covering indexes, and the mistakes that make a database ignore an index you created.

The book analogy

To find every mention of "recursion" in a 600-page book, you can read all pages or check the index at the back, which lists terms alphabetically with page numbers. A database table without an index works like the first method: to find rows where last_name = 'L77' it must read every row (a full table scan). A database index is a separate, sorted structure holding the indexed column's values and pointers to the rows, so the database can jump straight to the matches.

Most indexes are B-trees (balanced trees). Searching one takes a handful of steps even for millions of rows, because each step discards most of the remaining data. That is the difference between reading 100,000 rows and reading about 50.

The test table

CREATE TABLE employees (
  employee_id   INT AUTO_INCREMENT PRIMARY KEY,
  first_name    VARCHAR(30),
  last_name     VARCHAR(30),
  email         VARCHAR(60),
  department_id INT,
  salary        INT,
  hired         DATE,
  status        VARCHAR(10)
);

I loaded 100,000 generated rows: 2,000 distinct last names (about 50 people per name), unique e-mail addresses like user123@example.com, salaries from 30,000 to 99,900, and hire dates over about eight years. 90% of the rows have status active. Run EXPLAIN before a query to see how MySQL will execute it; the columns that matter are:

Column	Meaning
type	ALL = full scan (bad on large tables); ref, range, const = index used
key	the index chosen (NULL means none)
rows	the optimiser's estimate of rows it must examine
Extra	extra notes: Using where, Using index, Using filesort

Before: no index

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

type	key	rows	Extra
ALL	NULL	100005	Using where

MySQL will look at all 100,000 rows to find about 50.

Single-column index

CREATE INDEX idx_lastname ON employees (last_name);
EXPLAIN SELECT * FROM employees WHERE last_name = 'L77';

type	key	rows	Extra
ref	idx_lastname	50	NULL

The scan turned into an index lookup that examines about 50 rows. That is the whole idea: the same query, a thousand times less work.

Types of index

Type	What it does	Notes
Primary key	uniquely identifies each row; cannot be NULL	one per table; in InnoDB the table data is stored in primary-key order (the clustered index)
Unique index	like an index, but rejects duplicate values	CREATE UNIQUE INDEX uq_email ON employees (email)
Ordinary (secondary) index	speeds up lookups; allows duplicates	each entry also stores the primary key value
Composite index	one index on several columns	order of columns matters (next section)
Full-text index	word search inside text	used with MATCH ... AGAINST, not LIKE
Spatial index	geographic data	for point and polygon types
Hash index	exact-match lookups only	MEMORY tables and some other databases; InnoDB builds an adaptive hash internally

A unique index also protects data. Inserting a duplicate e-mail now fails:

CREATE UNIQUE INDEX uq_email ON employees (email);
INSERT INTO employees (first_name, last_name, email) VALUES ('X', 'Y', 'user5@example.com');

ERROR 1062 (23000): Duplicate entry 'user5@example.com' for key 'employees.uq_email'

Clustered versus non-clustered

In InnoDB (MySQL's default engine), the primary key is the clustered index: the rows themselves are stored in that order, so a primary-key lookup goes straight to the data. Every other index is non-clustered: it stores the indexed values plus the primary key, and MySQL uses that key to fetch the row (an extra step). SQL Server and others let you choose which index is clustered. In the interview answer, say: one clustered index per table (it defines the physical order), many non-clustered ones.

Composite indexes and the leftmost-prefix rule

A composite index on (last_name, first_name) is sorted by last name first, then by first name within each last name, like a phone book. It helps queries that start from the left:

CREATE INDEX idx_name ON employees (last_name, first_name);

Query	type	key	Why
WHERE last_name='L77' AND first_name='F77'	ref	idx_name (key_len 246)	uses both columns
WHERE last_name='L77'	ref	idx_name (key_len 123)	uses the leftmost column
WHERE first_name='F77'	ALL	NULL	skips the leftmost column, so no index use

(In this test I had dropped the single-column idx_lastname first, so the optimiser had only the composite to choose from. With both present, MySQL may pick the narrower one.) Looking up a phone book by first name alone is hopeless, and so is the index. This leftmost-prefix rule is why column order in a composite index is a design decision: put the column you always filter on first, and prefer the more selective one when both are always used.

Equality columns should come before range columns. For WHERE a = 1 AND b > 5, an index on (a, b) works well; (b, a) does not.

Covering indexes

If an index contains every column the query needs, MySQL answers from the index alone and never touches the table:

EXPLAIN SELECT last_name, first_name FROM employees WHERE last_name = 'L77';

type	key	rows	Extra
ref	idx_name	50	Using index

Using index is the sign of a covering index. This is why selecting only the columns you need (not SELECT *) can matter: SELECT * forces a lookup of the full row for every match.

When the index is NOT used

Creating an index does not guarantee it will be used. These are the classic reasons.

1. A function or calculation on the column

CREATE INDEX idx_hired ON employees (hired);
EXPLAIN SELECT * FROM employees WHERE YEAR(hired) = 2020;

type	key	rows
ALL	NULL	100005

The database cannot use the sorted dates when you ask about YEAR(hired). Rewrite the condition as a range on the bare column:

EXPLAIN SELECT * FROM employees
WHERE hired >= '2020-01-01' AND hired < '2021-01-01';

type	key	rows (estimate)	Extra
range	idx_hired	21736	Using index condition

The same applies to salary + 100 = 40100: with an index on salary, rewrite it as salary = 40000. In my test the first form scanned the whole index (type: index, about 100,000 rows) while the second looked up directly (type: ref, 143 rows).

2. A leading wildcard in LIKE

CREATE INDEX idx_email ON employees (email);
EXPLAIN SELECT * FROM employees WHERE email LIKE '%9@example.com';
EXPLAIN SELECT * FROM employees WHERE email LIKE 'user99%';

Pattern	type	key	rows
'%9@example.com'	ALL	NULL	100005
'user99%'	range	idx_email	1111

A sorted index can find "starts with" quickly but cannot find "ends with" or "contains". For word searches inside text, use a full-text index.

3. Mismatched types

Comparing a text column with a number forces MySQL to convert every stored value:

EXPLAIN SELECT employee_id FROM employees WHERE email = 12345;

MySQL scanned the whole index (type: index, about 100,000 rows) instead of looking up a value. Compare text with text and numbers with numbers; the other direction (employee_id = '5') is harmless because the constant is converted, not the column.

4. Skipping the leftmost column

Shown above for composite indexes.

5. Low selectivity

An index on status has only two distinct values:

+----------+----------+
| status   | COUNT(*) |
+----------+----------+
| active   |    90000 |
| inactive |    10000 |
+----------+----------+

For status = 'active', 90% of the table matches, and jumping back and forth through an index to fetch 90,000 rows is slower than reading the table in order, so the index adds little. Indexes pay off when they let the database skip most of the table. Columns with few distinct values (gender, yes/no flags, status) are poor candidates on their own, though they can be useful inside a composite index or when the wanted value is rare (like inactive here).

6. OR conditions on unindexed columns

WHERE last_name = 'L77' OR salary = 30500 can use two indexes together (MySQL's index merge, which showed Using sort_union(idx_name,idx_salary) in my test) but only if both sides have an index. One unindexed side forces a full scan.

Indexes help sorting too

EXPLAIN SELECT * FROM employees ORDER BY salary LIMIT 5;

Without an index on salary: type ALL, 100,005 rows, Using filesort (the whole table is sorted). After CREATE INDEX idx_salary ON employees (salary): type index, key idx_salary, 5 rows, no sort, because the index is already in order and MySQL reads the first five entries. Indexes also speed up GROUP BY and MIN/MAX.

The cost of indexes

Indexes are not free:

• Writes get slower. Every INSERT, UPDATE of an indexed column and DELETE must also update each index.
• They take disk space and memory. A composite or wide-text index can be as large as the data.
• Too many indexes confuse the optimiser and waste effort. Unused indexes are pure cost.

The practical approach: index the columns used in WHERE, JOIN ... ON, ORDER BY and GROUP BY of your real, frequent queries, check with EXPLAIN, and drop indexes that nothing uses. SHOW INDEX FROM employees; lists them with their cardinality (estimated number of distinct values), and DROP INDEX idx_status ON employees; removes one. Foreign key columns should be indexed (InnoDB creates the index for you if you forget), and tables that are written to constantly but rarely read need fewer indexes than reporting tables.

Index checklist

1. Run EXPLAIN on the slow query and look for type: ALL.
2. Index the columns in WHERE and JOIN conditions, starting with the most selective.
3. For several columns, build one composite index in the right order instead of several single ones.
4. Avoid wrapping indexed columns in functions; rewrite as ranges.
5. Avoid leading wildcards in LIKE.
6. Select only the columns you need, so covering indexes can work.
7. Re-check after the table grows; plans change with data size and statistics (ANALYZE TABLE).

Common interview questions

• What is an index and why is it used? A sorted structure that lets the database find rows without scanning the table.
• What is the difference between clustered and non-clustered indexes? The clustered index is the table's physical order (one per table); non-clustered indexes are separate structures pointing to the rows.
• What is a covering index? An index that contains all columns a query needs.
• Why might the database ignore my index? Functions on the column, a leading wildcard, type mismatch, the leftmost-prefix rule, low selectivity.
• What are the disadvantages of indexes? Slower writes, extra storage, maintenance.

Next steps

Indexes matter most together with good queries, so continue with SQL performance optimisation and the design ideas in normalisation. The index choices for joins are explained in the joins post, and the MySQL cheat sheet lists the index commands. To practise on a real schema, take the MySQL course.