MySQL Index Types

2016-04-24

Indexes in MySQL are data structures that improve the speed of data retrieval operations on database tables. Different types of indexes serve various purposes. Below are the main types of indexes in MySQL.

Unique Index

A unique index ensures that all values in the indexed column(s) are distinct. It prevents duplicate values and can be used to enforce uniqueness constraints.

  • Usage: Ideal for columns like email addresses or usernames where duplicates are not allowed.
  • Creation: CREATE UNIQUE INDEX idx_unique_email ON users (email);

Ordinary Index (Non-Unique)

Also known as a standard or regular index, it allows duplicate values in the indexed column(s). It speeds up queries but does not enforce uniqueness.

  • Usage: Common for frequently queried columns like names or categories.
  • Creation: CREATE INDEX idx_name ON users (name);

FULLTEXT Index

Designed for full-text searches on text-based columns. It supports complex queries like searching for words or phrases within text data.

  • Usage: Best for columns containing large text fields, such as articles or descriptions. Supports MATCH() AGAINST() syntax.
  • Creation: CREATE FULLTEXT INDEX idx_fulltext_content ON articles (content);
  • Note: Primarily used with MyISAM engine; InnoDB supports it from MySQL 5.6+.

SPATIAL Index

Used for spatial data types like GEOMETRY or POINT. It enables efficient querying of spatial relationships.

  • Usage: For geographic data, such as finding locations within a certain area.
  • Creation: CREATE SPATIAL INDEX idx_spatial_location ON places (location);
  • Note: Supported in MyISAM and InnoDB (from MySQL 5.7+).

HASH Index

Uses a hash table for lookups, providing fast equality searches. It is the default index type for MEMORY storage engine.

  • Usage: Efficient for exact matches, but not suitable for range queries or sorting.
  • Creation: Implicitly created for MEMORY tables; for others, use with caution as it’s engine-specific.

Additional Index Concepts

  • Primary Key Index: Automatically created for primary key columns. It’s a unique index that identifies rows uniquely.
  • Foreign Key Index: Often created on foreign key columns to speed up joins.
  • Composite Index: Indexes multiple columns together, e.g., CREATE INDEX idx_composite ON users (first_name, last_name);
  • Clustered vs. Non-Clustered: In InnoDB, the primary key is clustered (data stored with index); others are non-clustered.

Best Practices

  • Use indexes on columns frequently used in WHERE, JOIN, or ORDER BY clauses.
  • Avoid over-indexing, as it increases storage and slows down writes.
  • Monitor performance with EXPLAIN queries.
  • For large tables, consider index maintenance.

References