Mysql database index creation rule _ MySQL

Source: Internet
Author: User
Mysql database index creation rule

Mysql database index creation rules

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. these are the basis for judging general index creation.

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.