What are the common index types in MySQL?

Mysql common index types are: 1. FULLTEXT; 2. HASH; 3. BTREE; 4. RTREE. FULLTEXT is a full-text index, currently only supported by the MyISAM engine. HASH indexes can be located once, so they are extremely efficient.

Mysql currently mainly has the following index types:

(Recommended tutorial: mysql tutorial)

FULLTEXT, HASH, BTREE, RTREE.

Detailed introduction:

1. FULLTEXT

is the full-text index, currently only supported by the MyISAM engine. It can be used in CREATE TABLE, ALTER TABLE, and CREATE INDEX, but currently only full-text indexes can be created on CHAR, VARCHAR, and TEXT columns.

Full-text index was not born together with MyISAM. It appeared to solve the problem of low efficiency of fuzzy query for text such as WHERE name LIKE "%word%".

2. HASH

Because HASH is unique (almost 100% unique) and has a key-value pair-like form, it is very suitable as an index.

HASH index can be located once and does not need to be searched layer by layer like a tree index, so it is extremely efficient. However, this efficiency is conditional, that is, it is only efficient under the "=" and "in" conditions, and is still not efficient for range queries, sorting, and combined indexes.

3. BTREE

The BTREE index is a method that stores the index value into a tree-shaped data structure (binary tree) according to a certain algorithm. Each query is from the entrance of the tree. Starting from root, traverse the nodes in sequence to obtain the leaf. This is the default and most commonly used index type in MySQL.

4. RTREE

RTREE is rarely used in MySQL and only supports the geometry data type. The only storage engines that support this type are MyISAM, BDb, InnoDb, NDb, and Archive.

Compared with BTREE, the advantage of RTREE lies in range search.

The above is the detailed content of What are the common index types in MySQL?. For more information, please follow other related articles on the PHP Chinese website!