MySQL set up some small rules for indexing

1, the table's primary key, the foreign key must have the index;

2, the data volume of more than 300 of the table should be indexed;

3. Tables that are often connected to other tables should be indexed on the connection field;

4. Fields that often appear in the WHERE clause, especially for large tables, should be indexed;

5, the index should be built on the field of high selectivity;

6, the index should be built on the small section, for large text fields or even long fields, do not build index;

7, the establishment of composite index needs careful analysis; try to consider using single-field indexes instead:

A, the correct choice of composite index of the main column field, generally is a better choice of fields;
B, how many fields of a composite index often appear in the WHERE clause at the same time? Is there very little or no single-field query? If it is, you can create a composite index, otherwise consider the single-field index;
C, if the composite index contains a field that often appears separately in the WHERE clause, it is decomposed into multiple single-field indexes;
D, if the composite index contains more than 3 fields, then carefully consider its necessity, consider reducing the composite field;
E, if the existing single-field index, and the number of composite indexes on these fields, you can generally delete the composite index;

8, frequent data operation of the table, do not set too many indexes;

9. Delete useless indexes and avoid negative impact on execution plan;

These are some common criteria for establishing an index. Word, the establishment of the index must be cautious, the need for each index should be carefully analyzed, to establish the basis. Because too many indexes and inadequate, incorrect indexes are not good for performance: Each index established on the table increases the storage overhead, and the index increases processing overhead for insert, delete, and update operations. In addition, too many composite indexes, in the case of single-field index, generally have no value; Conversely, it also reduces performance when data is being deleted, especially for tables that are frequently updated, with greater negative impact.

MySQL set up some small rules for indexing