MySQL where to build the index MySQL needs to index the place is which

Because too many indexes and inadequate, incorrect indexes are not good for performance: Each index created on a table increases storage overhead, and indexing increases processing overhead for inserts, deletes, and updates.

In addition, too many composite indexes, in the case of a single field index, generally have no value; Conversely, it also reduces the performance of data additions and deletions, especially for frequently updated tables.

Since the index is so important, under what circumstances should an index be indexed, and under what circumstances can an index be built?

Let's share the nine major scenarios where MySQL needs to be indexed:

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, often with other tables to connect the table, in the connection field should be indexed;

4, often appear in the WHERE clause in the field, especially large table fields, 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 and even long fields, do not build indexes;

7, the establishment of composite index needs careful analysis; Consider using a single field index instead:

A, the correct selection of the composite index of the main column field, is generally a better choice of fields;

b, do several fields of a composite index often appear in the WHERE clause at the same time? is a single field query very small or not? If so, you can establish a composite index, otherwise consider a single field index;

C, if the fields contained in the composite index often appear separately in the WHERE clause, they are decomposed into multiple single field indexes;

D, if the composite index contains more than 3 fields, consider the need to reduce the compound field carefully;

E, if both the Single field index, and the composite index on these fields, you can generally delete the composite index;

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

9, delete the useless index, to avoid negative impact on the implementation plan;