Follow us on Social Media:

MySQL Database Administrator

Boost Your Quick Knowledge in MySQL DBA Work, Concepts, Tips and Trick

Saturday, 27 January 2018


Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees.

A tree data structure that is popular for use in database indexes. The structure is kept sorted at all times, enabling fast lookup for exact matches (equals operator) and ranges (for example, greater than, less than, and BETWEEN operators). This type of index is available for most storage engines, such as InnoDB and MyISAM.
In B-Tree indexes, all the index column values are stored in shorted form.

Because B-tree nodes can have many children, a B-tree is not the same as a binary tree, which is limited to 2 children per node.

PRIMARY KEY - Index column must be assigned as Primary Key.
UNIQUE - Index column must store unique values.
INDEX - Refers to a normal non-unique index. Non-distinct values for the index are allowed, so the index may contain rows with identical values in all columns of the index.
FULLTEXT - Indexes are different from all of the above, and their behaviour differs significantly between database systems. FULLTEXT indexes are only useful for full text searches done with the MATCH() / AGAINST() clause, unlike the above three - which are typically implemented internally using b-trees (allowing for selecting, sorting or ranges starting from left most column) or hash tables (allowing for selection starting from left most column).
Where the other index types are general-purpose, a FULLTEXT index is specialized, in that it serves a narrow purpose: it's only used for a "full text search" feature.

Hash Index-
Contrast with hash index, which is only available in the MEMORY storage engine. The MEMORY storage engine can also use B-tree indexes, and you should choose B-tree indexes for MEMORY tables if some queries use range operators.
Hash index is used for Equal(=) operator only . In Hash Index, each index column values are stored in there hash code with shorted form. 
The use of the term B-tree is intended as a reference to the general class of index design. B-tree structures used by MySQL storage engines may be regarded as variants due to sophistication not present in a classic B-tree design. For related information, refer to the InnoDB Page Structure Fil Header section of the MySQL Internals Manual.



Anonymous said...

Hi! Very nice article, but I have some corrections:
* BTREE nodes have exactly 2 children.
* MEMORY does not support BTREE. There is no way we can optimize an operation other than = with MEMORY.
* HASH is also supported by MyISAM (and Aria if you use MariaDB).
* Also InnoDB sometimes converts BTREE indexes to HASH if Adaptive Hash Index feature is enabled. But this is transparent to the user, and specifying USING HASH explicitly has no effect.

Alok Singh said...

B-tree is not the same as a binary tree, which is limited to 2 children per node.

Please Like and Subscribe us ....

Please visit the website on daily basis , Your New articles will publish at any moment. Please Subscribe us for getting Latest Updates...