MySQL Indexes basics and MySQL Indexes types

Types of Indexes in MySQL

MySQL supports the following indexes

• B+ Tree indexes
• Hash indexes
• Full-Text index
• R Tree indexes

B+ Tree Indexes

1. Highly Useful(>90%) in many real-time use-cases 
2. Work for Equality WHERE clause condition filters (Ex age=35) and range Condition filters (Ex salary > 2000)
3. In some cases, MySQL optimizer uses it for avoiding costly temp tables and file sorting operations. 
4. The default use B+tree index for Primary key and Unique key constraints 
5. Occupy the same or more amount of data size for managing B+ tree indexes.  

Hash Index: 

1. Give good performance compare to B+ tree indexes
2. useful only for Equality WHERE clause condition filters (Ex age=35) 
3. Take less storage space
4. Not useful for many use cases 

Full-Text Indexes: 

1. Useful to find out keyword in a large text Ex: find out candidate skill set in a resume.
2. Create Full–text indexes only for VARCHAR, CHAR or TEXT columns
3. Need more storage space.
4. faster to load your data into a table that has no FULLTEXT index
5. Useful only selected use cases.  

Spatial Indexes: 

1. MySQL can create spatial indexes using syntax similar to that for creating regular indexes, but using the SPATIAL keyword
2. SPATIAL INDEX creates an R-tree index
3. A B-tree index on spatial values is useful for exact-value lookups, but not for range scans

Ex:

CREATE TABLE geom (g GEOMETRY NOT NULL SRID 4326) 
ALTER TABLE geom ADD SPATIAL INDEX(g);

Types of Indexes in MySQL

MySQL supports the following indexes

• B+ Tree indexes
• Hash indexes
• Full-Text index
• R Tree indexes

B+ Tree Indexes

B+ Tree index useful in many cases. It occupies the same amount of storage as data for storing indexes. Most of the cases B+ tree indexes can help to speed up your queries with equality condition filters (Ex age=35) and range condition filters (Ex salary > 2000). There are many rules we need to follow while creating a B+ tree index for speed up queries.