Design principles for MySQL database indexing

To make indexes more efficient to use, you must consider which fields to create indexes on and what types of indexes to create when you create an index.

1. Select a Uniqueness Index
The value of a uniqueness index is unique and can be used to determine a record more quickly through the index. For example, the Student table secondary number is a unique field. Establishing a unique index for the field can quickly determine the information for a student. If you use a name, there may be a phenomenon with the same name, which slows down the query.

2. indexing fields that often require sorting, grouping, and Union operations
It is often necessary to order by, GROUP by, distinct, and Union fields, and sorting operations can be a waste of time. If you index it, you can effectively avoid sorting operations.

3. indexing a field that is frequently used as a query condition
If a field is frequently used to make query criteria, the query speed of the field affects the query speed of the entire table. Therefore, indexing such a field can increase the query speed for the entire table.

5. try to use an index with a small amount of data
If the value of the index is long, the speed of the query is affected. For example, full-text retrieval of a field of type char (100) would take more time than a field of type char (10).

6. use prefixes as far as possible to index
If the value of an indexed field is long, it is best to index it with the prefix of the value. For example, text and blog type fields, full-text retrieval can be a waste of time. If you retrieve only the first few characters of a field, you can increase the retrieval speed.

7. Delete indexes that are no longer used or used infrequently
Some of the existing indexes may no longer be needed after the data in the table has been extensively updated, or if the data has been used in a different way. The database administrator should periodically identify these indexes and remove them, thereby reducing the impact of the index on the update operation.

Note: The ultimate goal of selecting an index is to make the query faster. The principle given above is the most basic criterion, but it can not be rigidly tied to the above criteria.
In the future study and work in the continuous practice. According to the actual situation of the application to analyze and judge, choose the most appropriate index method.

< reference:http://soft.chinabyte.com/database/413/12518913.shtml>

Design principles for MySQL database indexing