Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to build an index in the database". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The common rules for indexing are as follows:

1. The primary key and foreign key of the table must have an index

2. Tables with more than 300 data should have indexes

3. Tables that are often connected to other tables should be indexed on the join field

4. Fields that often appear in the Where clause, especially those in large tables, should be indexed.

5. The index should be built on fields with high selectivity.

6. The index should be built on small fields. For large text fields or even super-long fields, do not build indexes.

7. The establishment of composite index needs to be analyzed carefully; try to consider using single-field index instead of:

A. correctly select the main column field in the composite index, which is generally a field with good selectivity.

B. do several fields of the composite index often appear in the Where clause in the form of AND at the same time? Are there few or no single-field queries? If so, a composite index can be established; otherwise, a single-field index is considered.

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, then carefully consider its necessity and consider reducing the number of composite fields

E. if you have both a single-field index and a composite index on these fields, you can generally delete the composite index

8. Do not build too many indexes for tables with frequent data operations

9. Delete useless indexes to avoid negative impact on the execution plan

This is the end of the content of "how to build an index in the database". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.