MySQL CREATE INDEX

The CREATE INDEX statement in MySQL improves query performance by allowing faster lookups in a table. Indexes work like an optimized table of contents, making searches more efficient.

1. MySQL CREATE INDEX Syntax

CREATE INDEX index_name 
ON table_name (column1, column2, ...);

index_name – The name of the index.
table_name – The table where the index will be created.
column1, column2, ... – The column(s) to be indexed.

🔹 Indexes speed up queries but increase storage and insertion time.

2. Types of Indexes in MySQL

A. Single-Column Index

Indexes a single column.

CREATE INDEX idx_lastname ON employees (last_name);

✅ Speeds up queries that filter or sort by last_name.

B. Multi-Column (Composite) Index

Indexes multiple columns.

CREATE INDEX idx_name_dept ON employees (last_name, department);

✅ Optimizes queries filtering by both last_name and department.

C. UNIQUE Index

Ensures column values are unique.

CREATE UNIQUE INDEX idx_email ON users (email);

✅ Prevents duplicate email addresses.

D. FULLTEXT Index

Used for text searches.

CREATE FULLTEXT INDEX idx_description ON products (description);

✅ Optimizes searches in large text fields.

E. PRIMARY KEY (Automatically an Index)

ALTER TABLE customers ADD PRIMARY KEY (customer_id);

✅ Every primary key is indexed by default.

3. Checking Indexes in MySQL

Use the SHOW INDEX command:

SHOW INDEX FROM employees;

✅ Displays all indexes in the employees table.

4. Dropping an Index

To remove an index:

DROP INDEX idx_lastname ON employees;

✅ Deletes the idx_lastname index.

5. When to Use Indexes?

✅ Use indexes for:

Frequently used WHERE conditions.
Sorting with ORDER BY.
Joining tables on indexed columns.

❌ Avoid indexing:

Small tables (full table scan is faster).
Columns with low uniqueness (e.g., gender).
Too many indexes (slow inserts/updates).

6. Performance Test: With vs. Without Index

Without Index (Slow Query)

SELECT * FROM employees WHERE last_name = 'Smith';

🔹 Full table scan, slow on large tables.

With Index (Fast Query)

CREATE INDEX idx_lastname ON employees (last_name);
SELECT * FROM employees WHERE last_name = 'Smith';

✅ Uses index, improving query speed significantly.

7. Conclusion

Indexes make queries faster but impact insert/update performance.
Use single, composite, UNIQUE, and FULLTEXT indexes wisely.
Always check existing indexes before creating new ones.

🚀 Optimize your MySQL queries with indexes today!

Souy Soeng

MySQL CREATE INDEX