What are the ways to create indexes in mysql?

You can create indexes when executing the CREATE TABLE statement, or you can use CREATE INDEX or ALTER TABLE alone to add indexes to the table.

1. Use CREATE INDEX to create. The syntax is as follows:

CREATE INDEX indexName ON tableName (columnName(length));

For example, we create an index with a length of 16 for the ip_address column:

CREATE INDEX index_ip_addr ON t_user_action_log (ip_address(16));

2. Use the ALTER statement to create an index. The syntax is as follows:

ALTER TABLE tableName ADD INDEX indexName(columnName);

The ALTER statement creates an index. An example of setting the index length is provided below:

ALTER TABLE t_user_action_log ADD INDEX ip_address_idx (ip_address(16));
 
SHOW INDEX FROM t_user_action_log;

3. Create an index when creating a table

CREATE TABLE tableName(  
  id INT NOT NULL,   
  columnName  columnType,
  INDEX [indexName] (columnName(length))  
);

The above is the detailed content of What are the ways to create indexes in mysql?. For more information, please follow other related articles on the PHP Chinese website!