MySQLSQL optimization: an out-of-the-box Index _ MySQL

MySQLSQL Optimization: An index that is out of the way. this is the 4th SQL optimization article.

Here, I mainly express my point of view that if an index does not exist, it should be eliminated, leaving it in the way.

During the period from 15:39:01 to 15:55:00, we found two problems in a business system:

This phenomenon is also very common in the database, that is, a slow query, the initiator, the execution of ttrojan slow, the subsequent query should be blocked quickly, resulting in the emergence of a series of long queries

After diagnosis, we found that a dateline index exists in a table. this index will cause the MySQL Optimizer to select an incorrect execution plan, resulting in a large number of SQL congestion in the future. about 5000 queries blocked each other.

If the dateline index is not used, the effect is good. the following comparison:

1. Comparison of execution plans

2. Comparison of Execution time:

Therefore, our feedback to developer shoes is that the dateline index is erased.

At first, we didn't know whether the index was provided for other queries, so we were scared that it would cause other queries to slow down.

However, after this period of time, no other queries are actually using this index.

Indexing is a good thing, but don't be greedy.

Good Luck!