Mysql database index creation rules

Mysql database index creation rules 1. The primary key and foreign key of the table must have an index; www.2cto.com 2. the table with more than 300 of the data volume should have an index; 3. The table that is frequently connected to other tables, indexes should be created on the connected fields; 4. Fields frequently appearing in the Where clause, especially those in large tables, should be indexed; 5. The indexes should be created 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 compound indexes should be carefully analyzed; 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 separately contained in the Where clause, they are decomposed into multiple single field indexes; d. If the composite index contains more than three fields, consider the necessity carefully and reduce the composite fields. E. If there is a single field index, compound indexes on these fields can be deleted. 8. Do not create too many indexes for tables that frequently perform data operations. 9. Delete useless indexes, avoid negative impact on execution plans. The above are the basis for determining general indexes. 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.