How to make the use of database indexes more efficient?

Indexes have a crucial impact on query speed. Understanding indexes is also the starting point for database performance tuning. During actual operations, which fields in the table should be selected as indexes? In order to make the use of indexes more efficient, when creating an index, you must consider which fields to create the index on and what type of index to create. There are 7 major principles:

1. Select a unique index

The value of the unique index is unique, and a record can be determined more quickly through the index. For example, the middle school ID in the student table is a unique field. Establishing a unique index for this field can quickly determine a student's information. If you use names, there may be the same name, which will slow down the query speed.

2. Create indexes for fields that often require sorting, grouping, and union operations

For fields that often require operations such as ORDER BY, GROUP BY, DISTINCT, and UNION, sorting operations will waste a lot of time. If you index it, you can effectively avoid the sort operation.

3. Create indexes for fields that are often used as query conditions

If a field is often used as a query condition, the query speed of this field will affect the query speed of the entire table. Therefore, indexing such fields can improve the query speed of the entire table.

4． Limit the number of indexes

The more indexes, the better. Each index requires disk space. The more indexes, the more disk space is required. When the table is modified, it is troublesome to reconstruct and update the index. The more indexes, the more time-consuming it becomes to update the table.

5． Try to use an index with a small amount of data

If the index value is very long, the query speed will be affected. For example, a full-text search for a CHAR(100) type field will definitely take more time than a CHAR(10) type field.

6． Try to use prefixes to index

If the value of the index field is very long, it is best to use the prefix of the value to index. For example, full-text search for TEXT and BLOG type fields will be a waste of time. If only the first few characters of the field are retrieved, the retrieval speed can be improved.

7. Delete indexes that are no longer used or rarely used

After the data in the table is updated a lot, or the way the data is used is changed, some of the original indexes may no longer be needed. Database administrators should regularly identify these indexes and delete them to reduce the impact of the indexes on update operations.

Note: The ultimate purpose of selecting an index is to make the query faster. The principles given above are the most basic guidelines, but you cannot stick to the above guidelines. Readers should continue to practice in their future studies and work. Analyze and judge based on the actual situation of the application, and select the most appropriate indexing method.

For example, let’s say you are making a membership card system for a shopping mall. This system has a membership table (roughly the fields are as follows):

Member number INT
Member name VARCHAR(10)
Member ID number VARCHAR(18)
Member phone number VARCHAR(10)
Member address VARCHAR(50)
Member remark information TEXT

Then this member number, as the primary key, use PRIMARY

If the member name is to be indexed, then it is ordinary INDEX

If you want to build an index for the member’s ID card number, you can choose UNIQUE (unique, no duplicates allowed)

Member’s remarks information. If you need to build an index, you can choose FULLTEXT for full-text search. .

The above 7 points are to make the use of indexes more efficient. I hope it will be helpful to everyone.

Related recommendations:

In order to make the use of indexes more efficient

The difference between btree and hash indexes in MySQL

How to use mysql index optimization

The above is the detailed content of How to make the use of database indexes more efficient?. For more information, please follow other related articles on the PHP Chinese website!