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.
Automatically created when defining a PRIMARY KEY.
Ensures each row has a unique, non-null value.
Physically organizes the table (clustered index in InnoDB).
Ensures all values in the indexed column(s) are unique.
Allows a single NULL value unless otherwise restricted.
Used to speed up general lookups using WHERE conditions.
Does not enforce uniqueness.
Used for full-text searching on large text fields.
Supports natural language search and boolean mode.
Available for CHAR, VARCHAR, and TEXT columns.
Used for geometric or GIS data types (POINT, POLYGON, etc.).
Requires tables using the InnoDB storage engine.
Supports spatial functions like distance, intersections, etc.
An index created on multiple columns together.
Useful when queries filter using multiple fields.
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.