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 frequently connected to other tables, should be indexed on the Join field, 4, fields that are often present in the WHERE clause, especially fields of large tables, should be indexed; 5, the index should be built on a field with high selectivity; , for large text fields or even extra-long fields, do not index; 7. The establishment of composite indexes needs careful analysis; try to consider using single-field index instead of: A, the correct selection of the composite index of the main column field, generally is a better choice of fields; B, Do several fields of a composite index appear in the WHERE clause frequently at the same time? Is there very little or no single-field query? If so, you can create a composite index, otherwise consider the single-field index; C, if the compound index contains fields that often appear separately in the WHERE clause, it is decomposed into multiple one-field indexes; D, if the composite index contains more than 3 fields, consider the necessity, consider the reduction of composite fields; E, If there is a single field index, and the composite index on these fields, you can generally delete the composite index, 8, the data operation of the table frequently, do not set too many indexes, 9, delete useless index, to avoid negative impact on the execution plan;
MySQL index creation rule