Principles and Methods for database index creation

Indexes increase data access speeds. However, creating indexes also increases the processing time for insert, update, and delete operations and increases the storage space of physical disks. Therefore, whether to add an index to the data table or add an index to the fields in the data table is an issue that must be considered before creating an index. The authors will share with you some principles and methods for creating indexes for database tables.
 
1. indexes are created on certain columns in the database table. Therefore, when creating an index, you should carefully consider which columns can create an index and which Columns cannot create an index.

The following data columns are suitable for indexing:

1. primary key field. The system automatically creates a primary key index;

2. unique kye field. The system automatically creates the corresponding index;

3. the field defined by the foreign key constraint as the foreign key;

4. fields used to connect to the table in the query;

5. Frequent order by, group by, and distinct fields;

6. columns in the Where clause are frequently used;
 
7. fields that often need to be searched by range;

8. Create a composite index on multiple frequently accessed columns. Note that the order of composite indexes should be determined according to the frequency of use;

Columns with the following features are not suitable for index creation:
 
1. Indexes should not be created for columns that are rarely used or referenced in queries. This is because, since these columns are rarely used, there is an index or no index, and the query speed cannot be improved. On the contrary, the addition of indexes reduces the system maintenance speed and space requirements;
 
2. Do not create an index on a large number of fields with the same value. This is because the values of these columns are very small. For example, whether the table in an article is recommended or not. In the query results, the data rows in the result set account for a large proportion of the data rows in the table, that is, the proportion of data rows to be searched in the table is large. Adding such a field index does not significantly accelerate the search speed;
 
3. Indexes should not be added for columns defined as text, image, and bit data types. This is because the data volume of these columns is not long, and the data result is either large or small;
 
4. When the modification performance is much higher than the retrieval performance, you should not create an index. This is because the modification performance and retrieval performance are inconsistent. When an index is added, the search performance is improved, but the modification performance is reduced. When the index is reduced, the modification performance is improved and the retrieval performance is reduced. Therefore, when the modification performance is much higher than the retrieval performance, too many indexes should not be created.

2. Indexes occupy disk space, and creating unnecessary indexes is a waste. You must determine how to create an index based on your actual business conditions.

1. For a data table whose content is rarely updated but needs to be frequently queried, it is not called to create more indexes for it;

2. Exercise caution when creating necessary indexes for tables that require frequent updates. For a table with a large number of update operations, the number of indexes should generally not exceed 3, and the maximum number should be 5. Although indexes increase the access speed, too many indexes will affect data update operations;

3. regularly check the data table structure and delete indexes that are no longer used or rarely used. If the data in the table is updated in large quantities or the usage of the data is changed, some of the original indexes may no longer be needed. Database Administrators should regularly identify and delete these indexes to reduce the impact of indexes on update operations.

4. For composite indexes, indexes are created based on the frequency when fields appear in the query conditions. In a composite index, records are first sorted by the first field. For records with the same value on the first field, the system sorts the records based on the value of the second field, and so on. Therefore, this index can be used only when the first field of the composite index appears in the query condition. Therefore, placing a field with a high application frequency before a composite index will enable the system to use this index as much as possible and play its role.

5. For small tables, index creation may affect performance.

Articles you may be interested in

• Rational use of MySQL database indexes to make the database run efficiently
• MySQL index Operation Command (create index, re-index, query index, delete index) Summary
• Mysql database cache function analysis, debugging, and performance summary
• Differences between MySQL Database Engine MyISAM and InnoDB
• Summarize the causes and solutions for the slow MySQL Database Server
• Analyze the reasons why Baidu index volume is decreasing and how to increase Baidu index volume
• How to view current auto-increment values of database tables
• Improve database performance secret SQL optimization skills