Which fields do mysql need to index?

Fields that need to be indexed in mysql: 1. Fields that often appear in the Where clause, especially fields in large tables, should be indexed; 2. Tables that are often connected to other tables should be indexed in the connection field Indexes should be created on fields; 3. Indexes should be created on grouping fields or sorting fields; 4. Indexes should be created on highly selective fields.

#1. The higher the dispersion value of a certain field in the table, the more suitable the field is to be selected as a keyword for the index. Primary key fields and unique constraint fields are suitable for selection as index keys because the values ​​of these fields are very discrete. MySQL is thoughtful when handling primary key constraints and unique constraints. When a database user creates a primary key constraint, MySQL automatically creates a primary index (primary index), and the index name is Primary; when a database user creates a unique index, MySQL automatically creates a unique index (unique index). By default, the index name is The field name of the unique index.

2. Fields that take up less storage space are more suitable to be selected as keywords for the index. For example, integer fields take up less storage space than strings, so they are more suitable as index keys.

3. Fields with fixed storage space are more suitable to be selected as keywords for indexing. Compared with text type fields, char type fields are more suitable to be selected as index keys.

4. Indexes should be created for frequently used fields in the Where clause, indexes should be created for grouping fields or sorting fields, and indexes should be created for the joining fields of two tables.

5. Fields that are frequently updated are not suitable for creating indexes, and fields that do not appear in the where clause should not be indexed.

6. Tables that are often connected to other tables should have indexes on the connection fields;

7. Indexes should be built on highly selective fields;

8. Indexes should be built on small fields. Do not build indexes on large text fields or even long fields;

The above is the detailed content of Which fields do mysql need to index?. For more information, please follow other related articles on the PHP Chinese website!