What are the techniques for creating indexes in mysql?

Mysql index creation skills: 1. Create an index for the primary key column; 2. Create an index for the columns that often need to be sorted, grouped and joined; 3. Create an index for the columns that often need to be used as query conditions; 4. Try to select columns with high distinction as indexes; 5. Create indexes for columns that do not participate in calculations.

This article will introduce some columns that are more suitable for creating indexes. Of course, there are also some situations that need to be paid attention to when creating indexes.

Recommended courses: MySQL Tutorial

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 certain 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 (columns) 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 (columns) that are often used as query conditions

If a field is often used as query conditions, 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. Try to choose columns with high distinction as index

The formula for distinction is count(distinct col)/count(*), which means the fields are not repeated. Ratio, the larger the ratio, the fewer records we scan. The distinction of a unique key is 1, while some status and gender fields may have a distinction of 0 in the face of big data. Then someone may ask, does this ratio have any empirical value? ? The usage scenarios are different, and this value is difficult to determine. Generally, we require fields that need to be joined to be above 0.1, that is, an average of 10 records will be scanned

8. The index column cannot participate in the calculation, and the column must be kept "Clean"

For example, from_unixtime(create_time) = '2014-05-29' cannot use the index. The reason is very simple. The b-tree stores all field values ​​​​in the data table, but When retrieving, you need to apply functions to all elements to compare, which is obviously too costly. So the statement should be written as create_time = unix_timestamp('2014-05-29');

9. Try to expand the index as much as possible, do not create a new index
For example, there is already an index of a in the table , now you want to add the index of (a, b), then you only need to modify the original index

Note: The ultimate purpose of selecting the 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.

The above is the detailed content of What are the techniques for creating indexes in mysql?. For more information, please follow other related articles on the PHP Chinese website!