52
What are the different types of indexes in MySQL?
Level: Advanced — Knowing which index type fits which scenario is a hallmark of a strong candidate.
MySQL supports several index types, each optimized for a specific purpose. Choosing the right one is key to query performance.
The Main Index Types
- Primary Index (Clustered): Created automatically on the PRIMARY KEY. Defines the physical order of rows. Only one per table.
- Unique Index: Ensures all values in the column are unique (allows one NULL).
- Regular / Non-Unique Index: A standard index to speed up lookups; duplicates allowed.
- Composite (Multi-column) Index: An index on two or more columns together.
- Full-Text Index: For fast text searching in large text columns (using MATCH...AGAINST).
- Spatial Index: For geometry/GIS data types (points, polygons).
Clustered vs Non-Clustered: InnoDB stores the actual row data with the primary key (clustered index). All other indexes are "secondary" (non-clustered) and point back to the primary key.
Index Types Summary
| Index Type |
Purpose |
Duplicates? |
| Primary (Clustered) |
Row identity & physical order |
No |
| Unique |
Enforce uniqueness |
No (one NULL ok) |
| Regular |
Speed up lookups |
Yes |
| Composite |
Multi-column queries |
Yes |
| Full-Text |
Text search |
Yes |
| Spatial |
Geographic data |
Yes |
Underlying Structures
- B-Tree: The default for most indexes — great for equality and range queries.
- Hash: Used by the MEMORY engine — extremely fast for exact matches, but not for ranges.
Quick Example
-- Unique index
CREATE UNIQUE INDEX idx_email ON users(email);
-- Regular (non-unique) index
CREATE INDEX idx_lastname ON employees(last_name);
-- Composite index (order of columns matters!)
CREATE INDEX idx_dept_sal ON employees(dept_id, salary);
-- Full-text index for text search
CREATE FULLTEXT INDEX idx_content ON articles(body);
SELECT * FROM articles
WHERE MATCH(body) AGAINST('database performance');
-- Spatial index (requires a NOT NULL geometry column)
CREATE SPATIAL INDEX idx_location ON places(coordinates);
Interviewer tip: Name the six index types, then add depth by explaining B-Tree vs Hash structures and the clustered vs non-clustered distinction — this shows senior-level understanding.