How to add index in mysql

In mysql, you can add indexes to fields in the table by using the SQL statement alter table.

1. Add PRIMARY KEY (primary key index)

mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2. Add UNIQUE (unique index)

mysql>ALTER TABLE `table_name` ADD UNIQUE ( 
`column` 
)

3. Add INDEX (normal index)

mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4. Add FULLTEXT (full-text index)

mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

5. Add multi-column index

mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

The following is more detailed Method

You can use the SQL statement alter table in MySQL to add indexes to fields in the table.

The basic syntax for using the alter table statement to add indexes to fields in the table is:

ALTER TABLE <表名> ADD INDEX (<字段>);

Example: Let’s try to add an index to the t_name field in test.

mysql> alter table test add index(t_name);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

After successful execution, let’s take a look at the results.

mysql> describe test;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| t_id       | int(11)     | YES  |     | NULL    |       |
| t_name     | varchar(50) | NO   | MUL | NULL    |       |
| t_password | char(32)    | YES  |     | NULL    |       |
| t_birth    | date        | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

The result can be seen that the Key column of the t_name field has changed from blank to MUL. What does this MUL mean? A brief explanation: If the Key is MUL, then the value of the column can be repeated. The column is the leading column (first column) of a non-unique index or is a component of a unique index but can contain the null value NULL.

The above is the detailed content of How to add index in mysql. For more information, please follow other related articles on the PHP Chinese website!