An index is a sorted data structure that lets MySQL find rows without scanning the whole table — the same idea as the index at the back of a book.
Creating indexes
CREATE INDEX idx_dept ON students(department);
CREATE UNIQUE INDEX idx_email ON students(email);
CREATE INDEX idx_dept_cgpa ON students(department, cgpa); -- composite
CREATE INDEX idx_name_prefix ON students(name_en(20)); -- prefix index
ALTER TABLE students ADD INDEX idx_admitted (admitted_on);
DROP INDEX idx_dept ON students;
SHOW INDEX FROM students\G
Index types
| Type | Used for |
|---|---|
| B-Tree (default) | Equality, ranges, sorting, prefixes |
| Hash | Exact equality only; MEMORY tables |
| Full-text | Natural-language search in text columns |
| Spatial (R-Tree) | Geometry columns |
Clustered vs secondary
In InnoDB the primary key is the clustered index — the table's rows are physically stored in primary-key order. Every secondary index stores the indexed columns plus the primary key value, so a secondary lookup does two steps: find the PK in the secondary index, then fetch the row from the clustered index. This is why a compact primary key matters and why covering indexes are fast: if the index already contains every column the query needs, the second step is skipped entirely.
CREATE INDEX idx_cover ON students(department, cgpa, name_en);
-- fully covered, EXPLAIN shows "Using index"
SELECT name_en, cgpa FROM students WHERE department = 'CSE';
The leftmost-prefix rule
A composite index on (a, b, c) can serve queries that filter on a, on a, b, or on a, b, c — but not on b alone or c alone.
CREATE INDEX idx_abc ON t(a, b, c);
WHERE a = 1 -- uses the index
WHERE a = 1 AND b = 2 -- uses the index
WHERE a = 1 AND b = 2 AND c = 3 -- fully used
WHERE b = 2 -- cannot use it
WHERE a = 1 AND c = 3 -- uses only the "a" part
Column order matters: put the most selective and most frequently filtered column first, and any range condition (>, <, BETWEEN) last, because everything after a range column stops being usable for lookups.
When an index is not used
WHERE YEAR(admitted_on) = 2024 -- function on the column
WHERE CAST(id AS CHAR) = '5' -- type mismatch
WHERE name_en LIKE '%uddin' -- leading wildcard
WHERE cgpa + 1 > 4 -- arithmetic on the column
Rewrite each so the column stands alone on one side of the comparison.
Functional and invisible indexes (MySQL 8)
CREATE INDEX idx_year ON students((YEAR(admitted_on))); -- functional index
ALTER TABLE students ALTER INDEX idx_dept INVISIBLE; -- test before dropping
An invisible index is still maintained but ignored by the optimizer — the safe way to check whether dropping an index will hurt.
The cost of indexes
Every index makes INSERT, UPDATE and DELETE slower and takes disk space, because it must be maintained on every write. Index the columns you filter, join and sort on; skip low-cardinality columns like a boolean flag, where a scan is cheaper than the lookup. Find unused indexes with:
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;