Database indexing common creation rules

The common rules for indexing are as follows:

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;

Database indexing common creation rules