MySQL Index Types: B-tree, Full-text, and Composite
Fast recall
Quick revision
Core answer
Assesses knowledge of query optimization fundamentals.
Interviewers want to know whether the candidate understands index selection, the leftmost prefix rule, and how Spring Data JPA queries interact with indexes.
Interviewers want to know whether the candidate understands index selection, the leftmost prefix rule, and how Spring Data JPA queries interact with indexes.
Interview-ready guide
Interview answer
MySQL supports several index types, but B-tree covers the vast majority of production query patterns. Understanding when other index types apply — and how composite index design works — is what actually moves query performance.
B-tree — the universal default
- B-tree keeps index values in sorted order, which means it handles equality (
=), ranges (BETWEEN,>,<), andORDER BYall in one structure — O(log n) traversal to find the starting entry, then a sequential scan along the leaf pages for ranges - When you run
CREATE INDEXwithout specifying a type, MySQL creates a B-tree; InnoDB also uses B-tree for its clustered primary key, which means secondary indexes always include the primary key as a hidden suffix
Composite indexes — design around your queries
- An index on
(status, created_at)servesWHERE status = ? AND created_at > ?efficiently, but notWHERE created_at > ?alone — because the leadingstatuscolumn must be present to establish a starting position in the sorted index - The most selective equality filter should lead; range conditions go last; columns only in SELECT can be added with
INCLUDE(MySQL 8.0.13+) for covering index behavior
FULLTEXT — for natural language search
- FULLTEXT indexes tokenize and index words, which means
MATCH(body) AGAINST('spring boot')finds documents containing those terms without a full-table scan orLIKE '%spring%'which can't use any index - Available on InnoDB since MySQL 5.6; supports boolean mode (
+spring -python) and natural language mode with relevance scoring
Spatial and Hash
- Spatial (R-tree) indexes support geometric queries — finding all locations within a bounding box; used with MySQL's geometry data types
- Hash indexes exist only in the MEMORY engine; InnoDB's adaptive hash index is internal and automatic, not user-created
Always validate with EXPLAIN that MySQL actually uses the index you expect — the optimizer sometimes prefers a full table scan when the table is small or statistics are stale.
MySQL Index Types: B-tree, Full-text, and Composite
Direct Answer
MySQL supports B-Tree (default), Hash, Full-text, and Spatial indexes. B-tree indexes power most range queries and equality lookups. Full-text indexes handle natural language search across text columns. Composite indexes (multi-column B-tree) accelerate queries filtering on multiple columns, following the leftmost prefix rule.
Code Example
Creating and using different MySQL index types
-- B-tree index (default): fast equality + range queries
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_status_created ON orders(status, created_at); -- composite
-- Composite index: leftmost prefix rule applies
-- This query USES the index (leftmost columns match)
SELECT * FROM orders WHERE status = 'PENDING' AND created_at > '2024-01-01';
-- This query also USES the index (leftmost column alone)
SELECT * FROM orders WHERE status = 'PENDING';
-- This query does NOT use the composite index efficiently (skips 'status')
SELECT * FROM orders WHERE created_at > '2024-01-01';
-- Full-text index: natural language search
CREATE FULLTEXT INDEX idx_ft_content ON articles(title, body);
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST ('Spring Boot performance' IN NATURAL LANGUAGE MODE);
-- Hash index: only in MEMORY engine, exact equality only
-- InnoDB uses adaptive hash indexes internally (automatic)
-- Covering index: all needed columns are in the index itself
CREATE INDEX idx_covering ON orders(user_id, status, total);
-- The following query is served entirely from the index (no table lookup)
SELECT status, total FROM orders WHERE user_id = 42;
-- Spring Data JPA: @Index annotation maps to DB index
-- @Table(indexes = @Index(name = "idx_email", columnList = "email"))
Common Mistakes & Pitfalls
Interviewers want to know whether the candidate understands index selection, the leftmost prefix rule, and how Spring Data JPA queries interact with indexes.
Check your understanding
Answer these without looking back at the article.
- What is a covering index and how does it eliminate the 'double lookup'?
- How does MySQL's query optimizer decide whether to use an index?
- How would you index a table for a Spring Data JPA findByStatusAndCreatedAtBetween query?
Related interview prep categories
2Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.