1. The table's primary key and foreign key must have an index;
2. Indexes should be available for tables with more than 300 of the data volume;
3. For tables that are often connected to other tables, indexes should be created on the connection fields;
4. fields that frequently appear in the Where clause, especially those in large tables, should be indexed;
5. The index should be built on highly selective fields;
6. Indexes should be built on small fields. Do not create indexes for large text fields or even extra-long fields;
7. The establishment of a composite index requires careful analysis. Try to replace it with a single field index:
A. correctly select the primary column field in the composite index, which is generally A highly selective field;
B. Do the fields of the composite index often appear in the Where clause in the AND mode? Are there very few or even no single-field queries? If yes, a composite index can be created; otherwise, a single field index is considered;
C. If the fields contained in the composite index are often contained in the Where clause, they are divided into multiple single-field indexes;
D. If the composite index contains more than three fields, consider the necessity and reduce the composite fields;
E. If there are both single-field indexes and composite indexes on these fields, you can delete composite indexes;
8. Do not create too many indexes for tables that frequently perform data operations;
9. Delete useless indexes to avoid negative impact on the execution plan;
The above are some general judgment bases for indexing. In a word, the establishment of indexes must be careful. The necessity of each index should be carefully analyzed and a basis should be established. Because too many indexes and inadequate or incorrect indexes have no benefits for performance: Each index created on a table increases storage overhead, indexes also increase processing overhead for insert, delete, and update operations. In addition, too many composite indexes generally have no value when there is a single-field index. On the contrary, they also reduce the performance when data is added and deleted, especially for frequently updated tables, the negative impact is greater.