MySQL CREATE index

1. In MySQL, there are four main types of indexes:

Primary key index (PRIMARY key)

Unique index (unique)

Regular Indexes (index)

Full-text index (fulltext)

2. Primary KEY index

A primary key index is the most common type of index in a relational database, and the primary role is to determine the location of a particular data record in the data table. We can add primary key after the field to set the primary key index for the field.

Attention:

1. It is a good idea to specify a primary key for each table, but not one that must be specified.

2. A table can only specify one primary key, and the value of the primary key cannot be null

3. Primary key can have multiple candidate indexes (for example, not null,auto_increment)

3. Unique index

A unique index, like a primary key index, prevents duplicate values from being created. However, the difference is that there can be only one primary key index per data table, but multiple unique indexes. We use the keyword unique to define a field as a unique index.

4. General Index

General indexing technology is the most important technology in relational data queries, and if you want to improve the performance of your database, index optimization should be considered first, as it will enable our database to achieve maximum performance improvements. The general index also has drawbacks:

1. Multiple disk space usage

2. Insert, delete, and modify actions are slowed down

3. Needs to be executed in sorted format on indexed columns

Create an index We can use the index and key keywords to create with the table.

5.

MySQL CREATE index