Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

What are the rules of mysql index establishment

2025-01-21 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

Mysql index establishment rules, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

The common rules for indexing in mysql 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 the composite index needs to be carefully analyzed; try to consider using a single-field index instead of: a, the correct selection of the main column field in the composite index 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 can be considered; C, if the fields contained in the composite index often appear separately in the Where clause, it will be 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 implementation plan; these are some common judgments when establishing an index. In a word, the establishment of the index must be cautious, the necessity of each index should be carefully analyzed, there should be a basis for establishment. Because too many indexes and inadequate and incorrect indexes are not good for performance: each index built on the table increases the storage overhead, and the index increases the 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, it will reduce the performance when data is added and deleted, especially for tables that are updated frequently.

The efficiency of your current methods is actually about the same, all very low, extremely low, the lowest.

First, if you do not improve efficiency, only simplify the program, I have suggested that you carry on the binary coding of the news category, and still use a field of features to indicate whether the lowest four binary bits are 1 to indicate whether it is picture news, headlines, home news, important news, such as 7 (binary 0111). Use bit operation when querying. The weights of picture news, headline news, front page news and important news are 8, 4, 2 and 1, respectively. If you call three news items that are photo news, you can directly use the sql statement:

Select * from news where features & 8pm 8 order by news_id desc limit 0Jol 3

If the three items are photo news and important news, you can directly use the sql statement:

Select * from news where features & 9, 9, 9, order by news_id desc limit, 010, 3.

See, you can query with one attribute, you can also query data with multiple attributes, the program is very convenient.

Second, the efficient way is to set up another table to record all kinds of news, store the data in multiple tables redundant, and query the news in which table you need. But the program will be very complex, save according to the category to store in different tables, the most troublesome to modify, if you modify the category also need to move the data from one table to another table. Of course, as an application like news, it is saved once, modified once or twice, and viewed thousands of times, which must be the key to optimize the query code for the sake of overall efficiency.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report