How to Create Index in MySQL: Syntax and Examples

To create an index in MySQL, use the CREATE INDEX statement followed by the index name, table name, and column(s) to index. Indexes speed up data retrieval by allowing faster searches on the specified columns.

Syntax

The basic syntax to create an index in MySQL is:

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

Here, index_name is the name you give to the index, table_name is the table where the index will be created, and column1, column2, ... are the columns to be indexed.

sql

CREATE INDEX index_name ON table_name (column1, column2);

Example

This example creates an index named idx_lastname on the last_name column of the employees table. It helps speed up queries filtering by last name.

sql

CREATE TABLE employees (
  id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  department VARCHAR(50)
);

CREATE INDEX idx_lastname ON employees (last_name);

-- Query to test index usage
EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';

Output

+----+-------------+-----------+-------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table     | type  | possible_keys | key         | key_len | ref   | rows | Extra       |
+----+-------------+-----------+-------+---------------+-------------+---------+-------+------+-------------+
|  1 | SIMPLE      | employees | ref   | idx_lastname  | idx_lastname| 102     | const |    1 | Using index |
+----+-------------+-----------+-------+---------------+-------------+---------+-------+------+-------------+

Common Pitfalls

Common mistakes when creating indexes include:

• Creating indexes on columns that are rarely used in queries, which wastes space and slows down writes.
• Using duplicate index names in the same table.
• Not considering the order of columns in multi-column indexes, which affects query optimization.

Always analyze your queries before adding indexes.

sql

/* Wrong: Duplicate index name */
CREATE INDEX idx_name ON employees (first_name);
CREATE INDEX idx_name ON employees (last_name); -- Error: duplicate index name

/* Right: Use unique index names */
CREATE INDEX idx_firstname ON employees (first_name);
CREATE INDEX idx_lastname ON employees (last_name);

Quick Reference

Command	Description
CREATE INDEX index_name ON table_name (column);	Create a new index on one or more columns
DROP INDEX index_name ON table_name;	Remove an existing index
SHOW INDEX FROM table_name;	List all indexes on a table
EXPLAIN SELECT ...	Check if a query uses an index

Key Takeaways

Use CREATE INDEX to speed up searches on specific columns.

Choose index names carefully to avoid duplicates.

Index columns used frequently in WHERE or JOIN clauses.

Multi-column indexes depend on column order for effectiveness.

Check query plans with EXPLAIN to verify index usage.