What are the different types of indexes available in MySQL?

Difficulty: 5/10

B-Tree indexes, Hash indexes, Full-text indexes

Types of Indexes Available in MySQL

MySQL provides several types of indexes to optimize query performance. Each type is designed for specific use cases depending on how data is stored, queried, or constrained.

1. PRIMARY KEY Index

1
Automatically created when defining a PRIMARY KEY.
2
Ensures each row has a unique, non-null value.
3
Physically organizes the table (clustered index in InnoDB).

2. UNIQUE Index

1
Ensures all values in the indexed column(s) are unique.
2
Allows a single NULL value unless otherwise restricted.

3. NORMAL / STANDARD Index (INDEX or KEY)

1
Used to speed up general lookups using WHERE conditions.
2
Does not enforce uniqueness.

4. FULLTEXT Index

1
Used for full-text searching on large text fields.
2
Supports natural language search and boolean mode.
3
Available for CHAR, VARCHAR, and TEXT columns.

5. SPATIAL Index

1
Used for geometric or GIS data types (POINT, POLYGON, etc.).
2
Requires tables using the InnoDB storage engine.
3
Supports spatial functions like distance, intersections, etc.

6. Composite Index

1
An index created on multiple columns together.
2
Useful when queries filter using multiple fields.
3
Follows leftmost prefix rule for index usage.

Choosing the correct index type depends on your workload: primary and unique indexes enforce constraints, normal indexes speed up filtering, FULLTEXT indexes support text search, and spatial indexes handle GIS operations.

Scenario Questions

0-2 years experience

Follow-up Questions