How to correctly create a MySQL Index

How to correctly create a MySQL Index

Indexing can improve data retrieval efficiency, reduce database I/O costs, and reduce database sorting costs. Sorting group operations mainly consume CPU resources and memory. Therefore, using indexes in sorting group operations can greatly reduce CPU resource consumption. This article will analyze how to correctly create MySQL Data indexes.

How can I determine whether to create an index?

1. Fields frequently used as query Conditions

We all know this. What is frequent teaching? Analyze all the SQL statements you run. It is best to list them one by one. After analysis, we find that some of the fields are used in most SQL statement queries, so we can create an index for them.

2. Fields with poor uniqueness are not suitable for indexing.

What is a field with poor uniqueness. Such as Status field and type field. Fields that only store fixed values, such as user logon status and message status. This involves the features of index scanning. For example, you can use an index to search for data with key values A and B, and use A to find the data that matches A. The data is displayed on page X and then continues scanning, the storage engine then discards the data on the X page and stores the data on the Y page, until all the data corresponding to A is searched, and then field B is searched, and the data corresponding to field B is found on page X. Then, the system scans the page X again, the X page is scanned twice or more times. Similarly, the same data page may be read and discarded multiple times, and read. This will undoubtedly add a great IO burden to the storage engine.

3. Fields updated too frequently are not suitable for index creation

When you create an index for this field, when you update the field data again, the database automatically updates its index, so when this field is updated too frequently, the index is constantly updated, and the impact of performance can be imagined. The fields updated after dozens of queries are compared to the index creation rules. If a field is updated multiple times in the same time period, you cannot create an index for it.

4. fields that do not appear in the where condition should not be indexed

I believe everyone knows this.

Articles you may be interested in

• Rational use of MySQL database indexes to make the database run efficiently
• Principles and Methods for database index creation
• MySQL index Operation Command (create index, re-index, query index, delete index) Summary
• Mysql Command for querying table Indexes
• Common knowledge about Mysql INDEX OPTIMIZATION
• Analyze the reasons why Baidu index volume is decreasing and how to increase Baidu index volume
• MySQL reference architecture from small websites to ultra-large scale websites
• Summary of the causes and solutions for slow mysql Server Query