intermediatePerformanceMySQL Specific100% Free

Indexing in MySQL

Definition

MySQL uses B-tree indexes by default (InnoDB), with additional support for FULLTEXT, SPATIAL, and Hash indexes (Memory engine only).

Introduction to Indexing in MySQL

MySQL's InnoDB storage engine uses clustered B-tree indexes where the primary key physically orders table data. This architecture makes primary key lookups extremely fast but has important implications for how secondary indexes and JOIN operations work.

Syntax

SQL

CREATE INDEX idx_name ON table(col);           -- Simple
CREATE UNIQUE INDEX idx ON table(col);          -- Unique
CREATE INDEX idx ON table(col1, col2, col3);    -- Composite
CREATE FULLTEXT INDEX idx ON articles(body);   -- Full-text
DROP INDEX idx_name ON table;

Examples

Composite Index Column Order

-- Composite index (department, hire_date)
CREATE INDEX idx_dept_hire
ON employees(department, hire_date);

-- USES the index (leftmost prefix)
SELECT * FROM employees WHERE department = 'Engineering';

-- USES the index (both columns)
SELECT * FROM employees
WHERE department = 'Engineering' AND hire_date > '2022-01-01';

-- Does NOT use the index (skips leftmost column)
SELECT * FROM employees WHERE hire_date > '2022-01-01';

MySQL composite indexes follow the leftmost prefix rule. The index helps queries using the first column or first+second column, but NOT the second column alone.

Try in Playground

FULLTEXT Index for Text Search

CREATE FULLTEXT INDEX idx_article_content
ON articles(title, body);

-- Natural language search
SELECT title, MATCH(title, body) AGAINST('database optimization') AS score
FROM articles
WHERE MATCH(title, body) AGAINST('database optimization');

-- Boolean mode search
SELECT title FROM articles
WHERE MATCH(title, body) AGAINST('+sql -oracle' IN BOOLEAN MODE);

FULLTEXT indexes in MySQL InnoDB support natural language and boolean mode searches. Much faster than LIKE '%keyword%' for text search.

Try in Playground

Common Mistakes

Using LIKE '%prefix' (starts with wildcard) — cannot use B-tree index; LIKE 'prefix%' can

Indexing every column individually instead of using composite indexes for multi-column queries

Forgetting that InnoDB secondary indexes include the primary key — affects composite index column counts

Not using index hints (USE INDEX, FORCE INDEX) when optimizer makes wrong choices

Frequently Asked Questions

How does InnoDB clustered index work in MySQL?

InnoDB stores table data in a B-tree ordered by the primary key — this is the clustered index. The primary key is not a separate structure; it IS the table. Secondary indexes store the indexed column(s) + primary key value as the row pointer. This means a secondary index lookup requires two B-tree searches: one in the secondary index, one in the clustered index (unless it's a covering index).

What is a covering index in MySQL?

A covering index includes all columns needed by a query, so MySQL can answer the query using only the index without reading the actual table rows (the clustered index). Example: CREATE INDEX idx_cov ON orders(customer_id, status, total) for SELECT status, total FROM orders WHERE customer_id = 5. Look for 'Using index' in EXPLAIN output.

How do you analyze index usage in MySQL?

Use EXPLAIN: EXPLAIN SELECT ... The 'key' column shows which index is used. 'rows' shows estimated rows scanned. 'Extra' column shows: 'Using index' (covering index, fast), 'Using where' (filter applied after index scan), 'Using filesort' (needs sort — consider adding ORDER BY column to index), 'Using temporary' (temp table — usually bad for large datasets).

Practice This in the SQL Playground

Write real queries, see live results, and master Indexing in MySQL hands-on. 100% free.

Open SQL Playground Browse All Lessons