Course: MySQL Training
Topic: Understanding B-Tree Indexes and Other Index Types
Course Link: MySQL Training Course – JustAcademy
Demo Link: Register for a Course Demo
Indexes are database structures that help MySQL locate records efficiently. They reduce the need to examine every row when searching, filtering, sorting, or joining data. Different index types are designed for different access patterns, so choosing the correct type is an important part of database design and performance optimization.
One of the most widely used index structures in MySQL is the B-tree family of indexes. In MySQL's InnoDB storage engine, ordinary indexes are generally implemented using B+ tree structures. These indexes support equality lookups, range searches, ordered retrieval, and many common database operations.
MySQL also supports other index types and access methods, including FULLTEXT indexes for text searching, SPATIAL indexes for spatial data, HASH indexes in specific contexts, and specialized index features such as functional and multi-valued indexes in supported versions.
A B-tree index is a balanced tree-based data structure that organizes keys so that records can be located without scanning the entire dataset. A balanced tree keeps its height controlled as keys are added or removed, allowing searches to remain efficient as the table grows.
In a database, index keys are stored in an ordered structure. MySQL can navigate the tree by comparing a search value with keys in the tree and following the appropriate path until it reaches the relevant leaf level.
A balanced tree maintains a structure in which leaf nodes remain at the same depth. This prevents one search path from becoming dramatically longer than another. When a node becomes too full or the structure changes, the database engine can reorganize the tree to maintain balance.
WHERE employee_id = 101.WHERE salary BETWEEN 30000 AND 60000.The terms B-tree and B+ tree are closely related, but they are not identical. A traditional B-tree can store keys and associated records or references in internal nodes as well as leaf nodes. A B+ tree keeps the full indexed records or row references at the leaf level, while internal nodes primarily guide the search.
InnoDB indexes are commonly described as B+ tree indexes. This structure is well suited to database storage because it supports ordered access and efficient range scans.
| Component | Description | Purpose |
|---|---|---|
| Root node | The starting node at the top of the tree. | Directs the search toward the correct branch. |
| Internal nodes | Nodes containing separator keys and child-page references. | Guide searches toward the appropriate leaf page. |
| Leaf nodes | The lowest level of the index structure. | Contain indexed keys and the relevant row data or row references, depending on index type. |
| Leaf-level links | Connections between neighboring leaf pages in B+ tree structures. | Help traverse ordered keys for range scans. |
[ 30 | 60 ]
/ | \
/ | \
[10, 20] [40, 50] [70, 80, 90]
Leaf Leaf Leaf
Leaf-level traversal:
[10, 20] <-> [40, 50] <-> [70, 80, 90]This is a simplified illustration of how separator keys guide searches. Real InnoDB indexes use pages containing many keys, and their layout depends on page size, key length, data distribution, and other storage details.
Suppose a table has an index on employee_id and a query searches for employee ID 70.
This process normally requires far fewer page accesses than scanning every row in a large table, although caching, selectivity, and the query plan affect actual performance.
InnoDB uses B+ tree structures for its primary and secondary indexes. Understanding the difference between these two index categories is essential for understanding how MySQL retrieves records.
In InnoDB, the clustered index determines how the table's row data is organized in the index structure. The primary key is normally used as the clustered index. The leaf pages of the clustered index contain the row data.
If a table has no declared primary key, InnoDB selects an appropriate non-NULL unique index as the clustered index when possible. If no suitable index exists, InnoDB creates an internal clustered index using a hidden row identifier.
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100),
department_id INT
) ENGINE = InnoDB;Here, employee_id is the primary key and normally defines the clustered index.
A secondary index is any InnoDB index that is not the clustered index. Its leaf entries contain the indexed column values and the corresponding primary-key value. MySQL can use that primary-key value to locate the full row in the clustered index when needed.
CREATE INDEX idx_employees_department
ON employees (department_id);This creates a secondary index on department_id. If a query retrieves only columns contained in the secondary index, MySQL may be able to answer it without fetching the complete clustered row. This is called a covering-index access pattern.
| Feature | Primary / Clustered Index | Secondary Index |
|---|---|---|
| Role | Organizes the table's row data in InnoDB. | Provides an additional access path. |
| Leaf contents | Contains the full row data. | Contains indexed values and primary-key values. |
| Number per table | One clustered index. | Multiple secondary indexes are possible. |
| Storage impact | Forms the primary organization of InnoDB row data. | Uses additional storage for each index. |
| Common use | Lookups by primary key. | Searches by other frequently queried columns. |
An equality search retrieves rows matching a specific value.
SELECT *
FROM employees
WHERE employee_id = 101;If employee_id is the primary key, MySQL can use the primary-key index to locate the row efficiently.
B-tree indexes are particularly useful for range conditions because the indexed keys are ordered.
SELECT *
FROM employees
WHERE employee_id BETWEEN 100 AND 200;MySQL can seek to the start of the range and scan relevant index entries until the range ends.
An index may help MySQL return rows in the requested order without performing a separate sort.
CREATE INDEX idx_employees_hire_date
ON employees (hire_date);
SELECT employee_id, employee_name, hire_date
FROM employees
ORDER BY hire_date;Whether the index avoids sorting depends on the query, ordering direction, index structure, and optimizer's chosen execution plan.
An index on a string column can often support a prefix pattern such as 'Sam%', where the search begins with a known prefix.
CREATE INDEX idx_employees_name
ON employees (employee_name);
SELECT *
FROM employees
WHERE employee_name LIKE 'Sam%';A leading wildcard such as '%Sam' generally prevents a normal B-tree index from efficiently locating the matching starting point.
Although B-tree indexes are common, different workloads may need different index types. The appropriate choice depends on the query operators, data types, storage engine, and MySQL version.
| Index Type | Main Purpose | Typical Use Case |
|---|---|---|
| B-tree / B+ tree | Equality, range, ordered lookup, and many join patterns. | Employee IDs, dates, prices, customer IDs. |
| HASH | Equality-based lookups in supported contexts. | In-memory tables or internal adaptive lookup optimization. |
| FULLTEXT | Search words and terms in text. | Articles, product descriptions, documents. |
| SPATIAL / R-tree | Spatial operations on geometric data. | Geographic points, boundaries, spatial intersections. |
| Unique index | Enforce uniqueness while providing an access path. | Email addresses, usernames, business identifiers. |
| Composite index | Index multiple columns in a defined order. | Filtering orders by customer and date. |
| Functional index | Index the result of a supported expression. | Lookups based on a normalized or calculated value. |
| Prefix index | Index the leading portion of a string value. | Long text-like string columns where prefix indexing is appropriate. |
| Multi-valued index | Index multiple values stored in a supported JSON array expression. | Specific JSON-array membership queries in supported MySQL versions. |
A HASH index uses a hash function to map key values to locations. It is designed primarily for equality lookups in implementations that support it, rather than ordered traversal.
BETWEEN.MEMORY tables support HASH indexes by default unless a different index type is specified.
CREATE TABLE session_lookup (
session_id VARCHAR(64) NOT NULL,
user_id INT NOT NULL,
INDEX idx_session_id USING HASH (session_id)
) ENGINE = MEMORY;This illustrates a HASH index on a MEMORY table. Confirm engine-specific restrictions and supported syntax for the MySQL version in use. InnoDB does not provide ordinary user-created HASH indexes in the same way that MEMORY tables do; it can use internal adaptive hash optimization automatically.
| Feature | B-Tree / B+ Tree | HASH |
|---|---|---|
| Equality lookup | Supported | Supported in applicable contexts |
| Range lookup | Supported | Not naturally supported |
| Ordered traversal | Supported | Not supported as an ordered index operation |
| ORDER BY assistance | Can help when index order matches | Does not provide sorted key order |
| Typical MySQL context | InnoDB primary and secondary indexes | MEMORY indexes and specific internal features |
A FULLTEXT index is designed for searching words and terms inside text-based columns. Unlike a normal B-tree index, which organizes complete column values in sorted order, a FULLTEXT index allows MySQL to search for relevant terms in supported text columns.
InnoDB and MyISAM support FULLTEXT indexes on supported CHAR, VARCHAR, and TEXT columns. InnoDB uses an inverted-index design that maps words to the documents or rows in which they appear.
CREATE TABLE articles (
article_id INT PRIMARY KEY,
title VARCHAR(200),
content TEXT,
FULLTEXT INDEX idx_articles_text (title, content)
) ENGINE = InnoDB;SELECT article_id, title
FROM articles
WHERE MATCH(title, content)
AGAINST ('database performance');Example of Boolean mode:
SELECT article_id, title
FROM articles
WHERE MATCH(title, content)
AGAINST ('+mysql -oracle' IN BOOLEAN MODE);In this example, the search requests documents containing “mysql” and excluding “oracle.” Actual results also depend on tokenization, stopwords, minimum token lengths, parser configuration, and the indexed content.
FULLTEXT is not the same as a general-purpose search engine. Features such as advanced typo tolerance, complex linguistic processing, and distributed search may require a dedicated search platform.
SPATIAL indexes are designed for spatial and geometric data. They help MySQL perform supported spatial searches involving objects such as points, lines, and polygons.
InnoDB and MyISAM use R-tree indexes for supported spatial index operations. An R-tree is designed to organize spatial objects using their spatial bounds rather than only a single ordered scalar value.
CREATE TABLE store_locations (
store_id INT PRIMARY KEY,
store_name VARCHAR(100),
location POINT NOT NULL SRID 4326,
SPATIAL INDEX idx_store_location (location)
) ENGINE = InnoDB;This table stores a point for each store and defines a spatial index. Supported spatial reference systems and restrictions should be checked against the MySQL version being used.
INSERT INTO store_locations
(store_id, store_name, location)
VALUES (
1,
'Central Store',
ST_GeomFromText('POINT(72.8777 19.0760)', 4326)
);For geographic coordinates, the order of coordinates matters. In this example, the point uses longitude followed by latitude. Confirm the axis-order behavior for the spatial reference system and function options used in your application.
Spatial indexes are specialized. They do not replace ordinary B-tree indexes for common numeric, date, or text-column filtering.
A unique index ensures that indexed key combinations do not contain duplicate non-NULL values. It can enforce data integrity while also providing an access path for queries.
CREATE UNIQUE INDEX idx_users_email
ON users (email);If the existing table contains duplicate email addresses, MySQL will not be able to create the unique index successfully until the conflicting data is resolved. Unique indexes can allow multiple NULL values under MySQL's standard unique-index behavior.
A composite index stores multiple indexed columns in a defined order. The column order affects which conditions the index can support efficiently.
CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date);This index may support:
-- Filter by customer
SELECT *
FROM orders
WHERE customer_id = 15;
-- Filter by customer and date range
SELECT *
FROM orders
WHERE customer_id = 15
AND order_date >= '2026-01-01'
AND order_date < '2027-01-01';The index is generally less suitable for a query filtering only on order_date, because that column is not the leftmost column in the index.
Consider the application's most common query patterns. Equality conditions, range conditions, sort requirements, and column selectivity all influence the design. There is no universal rule that the column with the most distinct values must always appear first; the best order depends on the workload and required access patterns.
A functional index indexes the result of an expression rather than only a column's stored value. It can be useful when queries frequently search using the same supported expression.
For example, an application may perform case-normalized email lookups using LOWER(email). In supported MySQL versions, an expression index can be created for this purpose.
CREATE INDEX idx_users_lower_email
ON users ((LOWER(email)));A query using a matching expression may then be able to use the functional index:
SELECT user_id, email
FROM users
WHERE LOWER(email) = '[email protected]';Functional index expressions have restrictions, and the query expression must match the indexed expression in a way the optimizer can recognize. Verify the exact syntax and supported expressions for your MySQL version.
A prefix index stores only the first specified number of characters or bytes of a string column, subject to the data type and character-set rules. Prefix indexing can reduce index size for long string values, but it may be less selective than indexing the full value.
CREATE INDEX idx_documents_title_prefix
ON documents (title(30));This example indexes a prefix of the title column. It may help some searches that use the leading portion of a title. MySQL may still need to examine candidate rows to confirm the complete condition.
Prefix indexes are not suitable for every column or query. For example, FULLTEXT indexes use a different mechanism and do not support ordinary prefix-length indexing of their indexed text columns.
MySQL supports multi-valued indexes for certain JSON expressions in supported versions. These indexes can index multiple scalar values from a JSON array, allowing specific membership and containment queries to be optimized.
They are a specialized feature, not a general replacement for normal indexes on relational columns. Their supported expressions and query patterns have restrictions, so use the documentation for the installed MySQL version when implementing them.
If a JSON array represents an important relational concept that is queried frequently, a normalized child table may sometimes be easier to maintain and query than storing all values inside JSON.
CREATE INDEX idx_products_category
ON products (category_id);For InnoDB, an ordinary index is a B-tree index. Specifying USING BTREE is generally unnecessary for ordinary InnoDB indexes.
CREATE INDEX idx_products_category_price
ON products (category_id, price);CREATE FULLTEXT INDEX idx_products_description
ON products (description);CREATE SPATIAL INDEX idx_locations_point
ON store_locations (location);The spatial column must satisfy the requirements of the storage engine and MySQL version. InnoDB spatial indexes have specific requirements, including restrictions related to NULL values and spatial reference systems.
SHOW INDEX FROM products;To inspect a table definition, use:
SHOW CREATE TABLE products;These statements help identify index names, indexed columns, uniqueness, column order, and index declarations.
Creating an index does not guarantee that MySQL will use it. The query optimizer estimates the cost of available plans and chooses the one it expects to be most efficient.
EXPLAIN
SELECT product_id, product_name
FROM products
WHERE category_id = 10;Review the following fields:
In supported MySQL versions, EXPLAIN ANALYZE executes the query and reports actual execution information alongside estimates. Use care when running it on expensive queries or production systems because the query really runs.
EXPLAIN ANALYZE
SELECT product_id, product_name
FROM products
WHERE category_id = 10;| Requirement | Potential Index Choice | Example |
|---|---|---|
| Find a customer by ID | Primary key or B-tree index | WHERE customer_id = 10 |
| Find orders between dates | B-tree index | WHERE order_date BETWEEN ... |
| Prevent duplicate usernames | Unique index | UNIQUE(username) |
| Filter by customer and date | Composite B-tree index | (customer_id, order_date) |
| Search words in article content | FULLTEXT index | MATCH(content) AGAINST (...) |
| Query geometric or geographic data | SPATIAL index | Indexed POINT or POLYGON column |
| Equality lookup in a suitable MEMORY table | HASH index may be appropriate | Session lookup table |
| Search using a normalized expression | Functional index, if supported | LOWER(email) |
Every additional index consumes storage and increases the work required to insert, update, and delete rows. Create indexes to support identified query patterns rather than indexing every column automatically.
An index on (department_id, status) is generally more useful for queries starting with department_id than for queries that filter only by status.
B-tree, HASH, FULLTEXT, and SPATIAL indexes have different capabilities. A FULLTEXT index is designed for word search, whereas a B-tree index is suitable for many equality and range conditions.
An index that appears sensible may not improve the actual workload. Use EXPLAIN and realistic test data to evaluate the chosen plan and query performance.
Index support varies by storage engine. For example, MEMORY supports HASH and BTREE indexes, while InnoDB uses B-tree structures for ordinary indexes and R-tree structures for spatial indexes.
Project objective: Design indexes for a simple e-commerce database to support customer lookups, order history, product filtering, text search, and geographic searches.
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100),
email VARCHAR(150),
city VARCHAR(100),
UNIQUE INDEX idx_customers_email (email)
) ENGINE = InnoDB;
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
status VARCHAR(30),
INDEX idx_orders_customer_date (customer_id, order_date)
) ENGINE = InnoDB;
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(150),
description TEXT,
category_id INT,
price DECIMAL(10,2),
INDEX idx_products_category_price (category_id, price),
FULLTEXT INDEX idx_products_description (description)
) ENGINE = InnoDB;SELECT customer_id, customer_name
FROM customers
WHERE email = '[email protected]';The unique index on email may support this lookup.
SELECT order_id, order_date, status
FROM orders
WHERE customer_id = 10
ORDER BY order_date;The composite index on (customer_id, order_date) may help filter and order the customer's records.
SELECT product_id, product_name, price
FROM products
WHERE category_id = 5
AND price < 1000
ORDER BY price;The composite index on (category_id, price) may support the filter and ordering requirements.
SELECT product_id, product_name
FROM products
WHERE MATCH(description)
AGAINST ('wireless headphones');The FULLTEXT index is designed to support this kind of text search.
EXPLAIN
SELECT order_id, order_date
FROM orders
WHERE customer_id = 10
ORDER BY order_date;Compare the estimated plan with the intended access pattern. Test with realistic data volumes before making conclusions about performance.
(department_id, hire_date).department_id and a date range.A B-tree index is an ordered, balanced tree-based structure that supports efficient searches, range lookups, and ordered access.
InnoDB uses B+ tree structures for ordinary primary and secondary indexes, and R-tree structures for supported spatial indexes.
The clustered index stores InnoDB's row data at its leaf level. A secondary index provides an additional access path and stores indexed values along with the primary-key value.
A HASH index uses a hash structure for equality lookups in supported contexts. It does not provide the same ordered traversal or range-query capabilities as a B-tree index.
A FULLTEXT index is designed to search words and terms within supported text columns using MySQL's full-text search features.
A SPATIAL index supports spatial operations on supported geometric data. InnoDB uses R-tree structures for spatial indexes.
A composite index contains two or more columns in a defined order. Its column order affects which query conditions can use it efficiently.
A unique index prevents duplicate indexed key combinations, subject to MySQL's handling of NULL values.
Yes. B-tree indexes can support suitable range conditions such as greater-than, less-than, BETWEEN, and some IN conditions.
Use EXPLAIN to inspect the optimizer's execution plan. In supported versions, EXPLAIN ANALYZE can show actual execution information by running the query.
Yes. Indexes use storage and require maintenance during data changes. Too many indexes can increase write costs and complicate query optimization.
The optimizer may estimate that a table scan or another plan is cheaper, or the query may not match the index's structure effectively.
SHOW INDEX to inspect existing indexes.EXPLAIN to review query execution plans.Understanding B-tree indexes and other MySQL index types helps developers design databases that are efficient, scalable, and easier to maintain. B-tree indexes are versatile and widely used for equality lookups, range searches, joins, and ordered retrieval. FULLTEXT indexes specialize in text search, SPATIAL indexes support geometric queries, and HASH indexes are available in specific storage-engine contexts.
The best index depends on the table structure, storage engine, query conditions, data distribution, and MySQL version. Inspect existing indexes, review execution plans, and test changes against realistic workloads. Effective indexing balances read performance with storage requirements and the cost of maintaining indexes during data modifications.
Continue learning with JustAcademy: Explore the MySQL Training Course or Register for a Course Demo.
