What should you pay attention to when using indexes in mysql?

Mysql needs to pay attention when using indexes: 1. NULL values ​​in columns will not be included in the index; 2. When indexing string columns, a prefix length should be specified; 3. Mysql queries only use An index; 4. Do not perform operations on columns; 5. Do not use NOT IN operations.

Notes:

(Recommended tutorial: mysql tutorial)

1. The index is not Columns that will contain NULL values

As long as the column contains NULL values, they will not be included in the index. As long as there is a column in the composite index that contains NULL values, then this column will be invalid for this composite index. . Therefore, when designing the database, we should not let the default value of the field be NULL.

2. Use short index

to index the string. If possible, you should specify a prefix length. For example, if you have a CHAR(255) column, if most values ​​are unique within the first 10 or 20 characters, then do not index the entire column. Short indexes not only improve query speed but also save disk space and I/O operations.

3. Index column sorting

MySQL query only uses one index, so if the index has been used in the where clause, the columns in order by will not use the index. Therefore, do not use sorting operations when the default sorting of the database can meet the requirements; try not to include sorting of multiple columns. If necessary, it is best to create composite indexes for these columns.

4. Like statement operation

Generally, the use of like operation is not encouraged. If it must be used, how to use it is also a problem. Like “�a%” will not use the index, but like “aaa%” will use the index.

5. Do not perform operations on columns

is as follows:

select * from users where YEAR(adddate)<2007;

will perform operations on each row, which will cause the index to fail and perform a full table scan, so We can change it to:

as follows:

select * from users where adddate<‘2007-01-01&#39;;

6. Do not use NOT IN and <> operation

The next sentence will use the index:

SELECT * FROM mytable WHERE username like'admin%'

You will not use it in the next sentence:

SELECT * FROM mytable WHEREt Name like'%admin'

Related recommendations: php training

The above is the detailed content of What should you pay attention to when using indexes in mysql?. For more information, please follow other related articles on the PHP Chinese website!