Some highlights of the mysql table index

1. The primary and foreign keys of the table must have indexes;

2. Tables with more than 300 data should have indexes;

3. For tables that are often connected with other tables, indexes should be established on the connection fields;

4. The fields that often appear in Where clauses, especially the fields of large tables, should be indexed.

5. The index should be built on the field with high selectivity;

6. Indexes should be built on small fields. For large text fields or even very long fields, do not build indexes.

7. The establishment of composite index requires careful analysis; Consider using a single-field index instead:

A, correctly select the main column field in the composite index, 1 is generally a selective field;
B, how often do several fields of the composite index appear simultaneously in the Where clause as AND? Are single-field queries rare or non-existent? If so, a composite index can be created; Otherwise consider a single-field index;
If the fields contained in the composite index often appear separately in the Where clause, it is decomposed into multiple single-field indexes;
If the composite index contains more than three fields, consider the necessity carefully and consider reducing the number of fields composed.
If there is both a single field index and a composite index on these fields, 1 can generally delete the composite index;

8, frequent data operation table, do not establish too many indexes;

9. Delete useless indexes to avoid negative impact on the implementation plan;

These are 1 of the most common criteria for indexing. In a word, the establishment of the index must be careful, the necessity of each index should be carefully analyzed, there should be a basis for establishment. Because too many indexes and inadequate, incorrect indexes have no performance benefit: every index created on a table increases the storage overhead, and indexes also increase the processing overhead for insert, delete, and update operations. In addition, too many composite indexes, in the case of a single field index, 1 generally have no value; Conversely, it can reduce performance when data is added or deleted, especially for frequently updated tables.