How to add an index to a field in MySQL?

In MySQL, there are two ways to add an index to a field:

1. Define indexes for fields when creating tables: When creating a table, you can specify indexes for fields using the CREATE TABLE statement. For example, the statement below demonstrates creating a table named users and adding a unique index to the username field.

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(50) NOT NULL,
  INDEX idx_username (username),
  UNIQUE INDEX idx_email (email)
);

The above statement uses the INDEX keyword to define a regular index, and the UNIQUE INDEX keyword to define a unique index.

1. To add an index using the ALTER TABLE statement: If the table has already been created, you can use the ALTER TABLE statement to add an index. For example, the following statement shows how to add a regular index to the username field of the users table.

ALTER TABLE users ADD INDEX idx_username (username);

The above statement used the ADD INDEX keyword to add a regular index named idx_username.

In addition to regular and unique indexes, other types of indexes such as FULLTEXT and spatial indexes can also be used. For more details, refer to the official MySQL documentation.