Indexes in MySQL are used to improve query performance by allowing faster retrieval of rows from large datasets. Unlike keys, indexes do not enforce uniqueness or relationships but help speed up searches significantly.

1. INDEX – Speed Up Searches

Indexes help MySQL find rows faster, especially on large datasets.

Syntax:

CREATE INDEX index_name ON table_name (column_name);

CREATE INDEX idx_name ON users(name);

2. FULLTEXT INDEX – Fast Text Searching

Used for searching large text content like articles or product descriptions with support for advanced search.

Syntax:

CREATE TABLE table_name (
  column_name TEXT,
  FULLTEXT (column_name)
);

CREATE TABLE posts (
  id INT,
  content TEXT,
  FULLTEXT (content)
);

3. SPATIAL INDEX – For Geographic Data

Used with geometry-based data types (like points or shapes) to index spatial values.

Syntax:

CREATE TABLE table_name (
  column_name GEOMETRY,
  SPATIAL INDEX (column_name)
);

CREATE TABLE places (
  id INT,
  location POINT,
  SPATIAL INDEX(location)
);

4. DROP INDEX – Remove an Index

You can delete any index you created if it’s no longer needed or optimized.

Syntax:

DROP INDEX index_name ON table_name;

DROP INDEX idx_name ON users;