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

What are the considerations when using indexes in mysql? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

When mysql uses the index, it should be noted that: 1, the column containing null value will not be included in the index; 2, when indexing the serial column, you should specify a prefix length; 3, the mysql query uses only one index; 4, do not operate on the column; 5, do not use the NOT IN operation.

Note:

1. The index will not contain columns with null values

As long as the column contains a null value, it will not be included in the index, and as long as one column in the composite index contains a null value, the column is invalid for the composite index. So let's not let the default value of the field be NULL when designing the database.

2. Use short index

Index the string column and specify a prefix length if possible. For example, if you have a column of CHAR, and if most of the values are unique within the first 10 or 20 characters, do not index the entire column. Short indexes can not only improve the query speed, but also save disk space and Imax O operations.

3. Sort the index column

The MySQL query uses only one index, so if the index is already used in the where clause, the columns in the order by will not use the index. Therefore, do not use the sort operation when the database default sorting can meet the requirements; try not to include multiple column sorting, and it is best to create a composite index on these columns if necessary.

4. Like statement operation

In general, the use of like operations is discouraged, and if you have to, how to use it is also a problem. Like "% aaa%" does not use an index, while like "aaa%" can use an index.

5. Do not operate on the column

It is as follows:

Select * from users where YEAR (adddate)

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.