Limitations and precautions for Mysql Indexes

The above content is about using Mysql indexes, but using Mysql indexes too much will cause some disadvantages. The following describes the disadvantages of Mysql indexes. For example, it will reduce the actual speed of updating tables to a certain extent.

Disadvantages of Mysql Indexes

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.

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

Considerations for using Mysql Indexes

Tips and notes for using indexes:

The index does not contain columns with NULL values.

As long as a column contains a NULL value, it will not be included in the 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, the column in order by will not use the Mysql 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 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 the Mysql index to fail and scan the entire table. Therefore, 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.