Shulou(Shulou.com)06/01 Report--

This article will explain in detail the methods to make the database index more efficient. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

The index has a vital impact on the speed of the query, understanding the index is also the starting point for database performance tuning, in the actual operation, which fields in the table should be selected as the index? In order to make the use of the index more efficient, when creating an index, you must consider which fields to create the index and what type of index to create. There are seven general principles:

1. Select the uniqueness index

The value of the uniqueness index is unique, and a record can be determined more quickly through the index. For example, the student number in the student table is a unique field. Establishing a unique index for this field can quickly determine the information of a student. If a name is used, there may be a phenomenon of the same name, which slows down the query speed.

2. Index fields that often require sorting, grouping, and federation operations

Fields for ORDER BY, GROUP BY, DISTINCT, and UNION operations are often required, and sorting operations can waste a lot of time. If you index it, you can effectively avoid sorting operations.

3. Index fields that are often used as query criteria

If a field is often used as a query condition, the query speed of that 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

It is not as many indexes as possible. Each index takes up disk space, and the more indexes you have, the more disk space you need. When modifying a table, it is troublesome to reconstruct and update the index. The more indexes you have, the more time it takes to update the table.

5. Use indexes with less data as much as possible

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

6. Try to use prefixes to index

If the value of the index field is long, it is best to use the prefix of the value to index it. For example, for fields of TEXT and BLOG types, full-text retrieval can be a waste of time. If you retrieve only the first few characters of the field, you can improve the retrieval speed.

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

After the data in the table is updated massively, or the way the data is used is changed, some of the original indexes may no longer be needed. Database administrators should periodically find these indexes and delete them, thereby reducing the impact of indexes on update operations.

Note: the ultimate goal of selecting an index is to make the query faster. The principles given above are the most basic principles, but we should not stick to them. Readers should continue to practice in their future study and work. Analyze and judge according to the actual situation of the application, and choose the most appropriate index method.

For example, for example, you are making a membership card system for a shopping mall. The system has a membership table (approximate fields are as follows):

Member number INT

Member name VARCHAR (10)

Member identity card number VARCHAR (18)

Member telephone number VARCHAR (10)

Member address VARCHAR (50)

Member remarks information TEXT

So this membership number, as the primary key, uses PRIMARY.

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

If you want to index the member ID card number, you can choose UNIQUE (unique, no repetition is allowed)

If you need to index member remarks, you can choose FULLTEXT, full-text search.

On the database index to make the use of more efficient methods to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.