Index Types: B-tree, Hash, Full-Text, Spatial

“Index type” can mean three different things in MySQL: the data structure, the role of the index and the kind of search it supports. Here they are, one at a time.

By data structure

For nearly every index you create in an InnoDB table, the structure is a B-tree. You can write USING BTREE, but it is the default.

Hash indexes

A hash index finds exact matches very fast, but cannot do ranges, sorting or prefixes, because hashing destroys order. MySQL supports explicit hash indexes only in MEMORY tables. InnoDB builds its own adaptive hash index in memory, automatically, for hot lookups. You do not control it per table, and can switch it on or off with innodb_adaptive_hash_index.

1
2

CREATE TABLE session_cache (token CHAR(16) NOT NULL, user_id INT NOT NULL, PRIMARY KEY (token) USING HASH) ENGINE = MEMORY;
SHOW CREATE TABLE session_cache\G

1
2
3
4
5
6
7
8

*************************** 1. row ***************************
       Table: session_cache
Create Table: CREATE TABLE `session_cache` (
  `token` char(16) NOT NULL,
  `user_id` int NOT NULL,
  PRIMARY KEY (`token`) USING HASH
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set

MEMORY tables lose their data when the server restarts, so they are for caches and temporary work, not for real data (Storage Engines).

By role (InnoDB)

Because every secondary index contains the primary key, a large primary key makes every index larger. That is one reason to use a short INT or BIGINT as primary key.

By kind of search

1
2
3
4
5
6
7
8
9

CREATE TABLE places (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    location POINT NOT NULL SRID 4326,
    SPATIAL INDEX sp_location (location)
);
INSERT INTO places (name, location) VALUES ('Pune', ST_GeomFromText('POINT(18.52 73.86)', 4326)), ('Mumbai', ST_GeomFromText('POINT(19.07 72.88)', 4326));

SELECT name, ROUND(ST_Distance_Sphere(location, ST_GeomFromText('POINT(18.52 73.86)', 4326)) / 1000) AS km_from_pune FROM places ORDER BY km_from_pune;

1
2
3
4
5
6
7

+--------+--------------+
| name   | km_from_pune |
+--------+--------------+
| Pune   |            0 |
| Mumbai |          120 |
+--------+--------------+
2 rows in set

Spatial work is a field of its own. This is only a glimpse: the distance between Pune and Mumbai in kilometres, with an R-tree index declared on the point column.

Which one should I use?

• Normal B-tree for 95 percent of cases.
• FULLTEXT when you search for words in text columns.
• SPATIAL for map and location queries.
• Hash only in MEMORY tables, which you rarely need.

Not an index type, but related: partitioning

Splitting a table into partitions is a different tool. It helps with maintenance (dropping old data fast) and sometimes with scanning by range (Partitioning).