Disadvantages of MySQL indexes and precautions when using Indexes

The following articles mainly introduce the disadvantages of MySQL indexes and the actual operations of MySQL indexes, which deserves our attention, we may not know that using indexes too much will cause abuse. Therefore, MySQL indexes also have their disadvantages:

Although the index greatly improves the query speed, it also reduces the speed of updating the table, such as performing INSERT, UPDATE, and DELETE operations on the table. When updating a table, MySQL not only needs to save data, but also stores the index file.

Index files that occupy disk space. This problem is not serious in general, but if you create multiple composite indexes on a large table, the index file will expand very quickly.

Indexing is only a factor to improve efficiency. If your MySQL database has a large amount of data, you need to spend time researching and creating the best MySQL indexes or optimizing query statements.

Notes for using Indexes

Tips and notes for using indexes:

The index does not contain columns with NULL values.

As long as the column contains a NULL value, it will not be included in the MySQL index. If a column in the composite index contains a NULL value, this column is invalid for this composite index. Therefore, do not set the default value of a field to NULL during database design.

Use short Indexes

Index a string or column. If possible, specify a prefix length. For example, if a CHAR (255) Column exists and multiple values are unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only increase query speed, but also save disk space and I/O operations.

Index column sorting

MySQL queries only use one index. Therefore, if an index is already used in the where clause, columns in order by will not use the index. Therefore, do not use the sorting operation when the database's default sorting can meet the requirements. Try not to include the sorting of multiple columns. It is best to create a composite index for these columns if necessary.

Like statement operation

Generally, like operations are not encouraged. If they are not usable, how to use them is also a problem. Like "% aaa %" does not use MySQL indexes, but like "aaa %" can use indexes.

Do not perform operations on columns

 

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

The operation will be performed on each row, which will cause index failure and scan the entire table, so we can change it

 

  
select * from users where adddate<‘2007-01-01’;  
 

Do NOT use not in and <> operations

The above section describes the MySQL index type.