What are the classifications of mysql indexes?

MySQL indexes are divided into the following types: 1. Ordinary index: matching value, range or prefix; 2. Unique index: ensuring unique value; 3. Primary key index: unique index of the primary key column; 4. Foreign key index : Points to the primary key of another table; 5. Full-text index: full-text search; 6. Hash index: equal match search; 7. Spatial index: geospatial search; 8. Composite index: search based on multiple columns.

MySQL Index Classification

Index is a structure in MySQL used to quickly find and retrieve data. They help reduce the time required to scan a data table by creating pointers to data in the table. There are the following types of indexes in MySQL:

1. Ordinary index (BTREE index)

• The most common index type.
• Can be used to match column value, range or prefix search.
• Can have multiple columns.

2. Unique index

• Ensure that the column value is unique in the table.
• Can speed up finding records based on unique columns.
• Can have multiple columns.

3. Primary key index

• Special type of primary key, unique and non-empty.
• MySQL automatically adds the table's primary key to the primary key index.

4. Foreign key index

• An index pointing to the primary key of another table.
• Can establish relationships between tables and enforce integrity constraints.

5. Full-text index (FTS index)

• For full-text search on text and numeric fields.
• Allows searching based on keywords or phrases.

6. Hash index

• maps key values directly to data pages.
• Applies only to equal match searches.
• Faster than BTREE index, but takes up more storage.

7. Spatial index

• is used to index geospatial data.
• Supports search based on location, range and distance.

8. Composite index

• An index composed of multiple columns.
• Can speed up searches based on multiple columns.
• The order and order of each column is important.

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