How to create an index in MySQL?

In MySQL, you can use the CREATE INDEX statement to create an index. The syntax format is as follows:

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

The UNIQUE keyword is optional and is used to create a unique index. index_name is the name of the index, table_name is the name of the table in which the index is created, and (column1, column2, …) are the columns on which the index is created.

For example, to create an index named idx_name for the name column in a table called users, you can use the following statement:

CREATE INDEX idx_name
ON users (name);

To create a unique index, you can add the UNIQUE keyword to the statement.

CREATE UNIQUE INDEX idx_name
ON users (name);

Creating an index can take up a significant amount of time and system resources, especially on large tables. Therefore, before creating an index, it is important to assess its impact on query performance and make sure to only create indexes for the most necessary columns.