? MySQL CREATE INDEX

⚙ Performance Optimization

⚡ Quick Overview

The CREATE INDEX statement in MySQL is used to create an index on one or more columns of a table. Indexes act like a fast lookup for rows and can dramatically speed up SELECT queries, especially on large tables. However, they also add some overhead to INSERT, UPDATE, and DELETE operations.

? Key Concepts

Index: A data structure that helps MySQL quickly find rows that match a given condition.
index_name: User-defined name for the index (e.g., idx_employee_name).
table_name: The table on which the index is created.
column_name: One or more columns that the index will be built on.
Read vs Write: Indexes speed up reads but can slow down writes because the index must be updated.

? Syntax

The basic syntax to create an index on a single column is:

? View Code Example

-- Create a simple index on one column
CREATE INDEX index_name
ON table_name (column_name);

You can also create an index on multiple columns (composite index):

? View Code Example

-- Create a composite index on multiple columns
CREATE INDEX index_name
ON table_name (column1, column2);

? Example

Example: creating an index on the Name column of an Employees table.

? View Code Example

-- Create an index on the Name column in Employees table
CREATE INDEX idx_employee_name
ON Employees (Name);

? What Happens Internally?

MySQL creates an index named idx_employee_name on the Name column.
When you run a query like: SELECT * FROM Employees WHERE Name = 'John'; MySQL can use the index to quickly find matching rows instead of scanning the entire table.
The index takes additional storage and will be updated every time Name changes or a new row is inserted/deleted.

✅ Tips & Best Practices

Create indexes on columns that are frequently used in WHERE, JOIN, ORDER BY, or GROUP BY clauses.
Avoid creating too many indexes on the same table; each index adds overhead to write operations.
Do not index columns that are rarely filtered or searched (e.g., description fields with long text).
For composite indexes, put the most selective (most unique) column first.
Periodically review and remove unused indexes to improve performance and save storage.

? Try It Yourself

Create an Employees table and insert sample data. Then:
1. Run a SELECT query with a WHERE condition on Name.
2. Create an index on Name.
3. Run the same query again and observe the performance using EXPLAIN.
Create a composite index on two columns (e.g., Department and JoiningDate) and run queries that use both columns in the WHERE clause.
Experiment with adding and dropping indexes using: DROP INDEX index_name ON table_name; and see how it affects query plans.