MySQL CREATE INDEX Statement

MySQL CREATE INDEX Statement

The CREATE INDEX statement in MySQL is used to create an index on one or more columns of a table to speed up data retrieval.
Indexes improve query performance, especially for large tables, by allowing the database to quickly locate rows without scanning the entire table.

⚠️ Indexes do not store data, only pointers to data. Excessive indexing may slow down INSERT, UPDATE, DELETE operations.

🔹 Syntax

• index_name → Name of the index

• table_name → Table on which the index is created

• column1, column2 → Columns included in the index

• Optional keywords:

  • UNIQUE → Ensures unique values

  • FULLTEXT → Used for text search

  • SPATIAL → For spatial data types

🔹 Example 1: Basic Index

• Creates an index idx_name on the name column of the Students table

• Queries like SELECT * FROM Students WHERE name='John'; will run faster

🔹 Example 2: Unique Index

• Ensures that email values are unique

• Cannot insert duplicate emails

🔹 Example 3: Multi-Column Index (Composite Index)

• Index on dept and marks columns

• Helps queries filtering both columns efficiently:

🔹 Example 4: Full-Text Index (Text Search)

• Optimized for searching words or phrases in large text columns

🔹 Key Points

1. Indexes improve SELECT query performance but may slow down INSERT/UPDATE/DELETE.

2. Primary Key and UNIQUE columns automatically create indexes.

3. Can create single-column or multi-column (composite) indexes.

4. Use DROP INDEX index_name ON table_name; to remove an index if needed.

5. Useful for frequently searched columns or columns used in JOINs.