Insufficient mysql Indexes

Although mysql index improves the query speed, it is not perfect. mysql index also has its own shortcomings. Let's take a look at it.

Disadvantages of mysql Indexes

Excessive use of indexes will cause abuse. Therefore, the index also has its disadvantages:
◆ Although the mysql index greatly improves the query speed, it also reduces the speed of updating tables, such as performing INSERT, UPDATE, and DELETE operations on tables. When updating a table, MySQL not only needs to save data, but also stores the index file.
◆ Create a mysql index that will 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

When using mysql indexes, you can follow these tips and precautions:
◆ 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 the string and 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.
◆ Sorting of index Columns
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 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 <'2014-01-01 ';
◆ Do NOT use not in and <> operations

MySQL database connection command

MySql connection string description

Three statuses of mysql triggers

Create MySQL trigger syntax

Mysql Aggregate functions