What are indexes and what are the different types in MySQL?
An index is a separate data structure that lets the database find rows without scanning the whole table. Trades disk space and write speed for read speed.
Primary index — on the primary key; in InnoDB this is the clustered index that physically orders the table.
Secondary index — non-clustered; stores the indexed columns plus a pointer to the primary key.
Unique index — enforces uniqueness in addition to indexing.
Composite index — multiple columns; only useful for queries matching the leftmost prefix.
Full-text index — for
MATCH ... AGAINSTtext search.Spatial / R-tree — geometric data.
Default structure — B-tree; hash indexes only on Memory engine.
-- Create index
CREATE INDEX idx_email ON users(email);
-- Unique index
CREATE UNIQUE INDEX idx_username ON users(username);
-- Composite index
CREATE INDEX idx_dept_salary ON employees(department, salary);
-- Prefix index (first 10 characters)
CREATE INDEX idx_name ON users(name(10));
-- Full-text index
ALTER TABLE articles ADD FULLTEXT INDEX ft_content(title, body);
SELECT * FROM articles WHERE MATCH(title, body) AGAINST('MySQL tutorial');
-- Check if index is used
EXPLAIN SELECT * FROM users WHERE email = 'john@test.com';
-- Look for: type=ref (index used) vs type=ALL (full scan — bad!)
-- Show indexes
SHOW INDEX FROM users;
-- Drop index
DROP INDEX idx_email ON users;B-Tree index on email enables fast lookups. Composite index (department, salary) supports queries filtering by department and/or sorting by salary. Prefix index indexes only the first 10 characters (saves space for long strings).
Full-text index enables natural language text search. EXPLAIN shows whether the query uses an index (ref/range) or scans all rows (ALL).
Know: B-Tree (default, most common), composite (field order matters — leftmost prefix rule), full-text (MATCH AGAINST), and covering index (all needed columns in index). EXPLAIN is the debugging tool: type=ALL is bad (full scan), type=ref/range is good (index used).
Trade-off: faster reads, slower writes.