The efficiency of mysql subquery union and in, and the efficiency of mysqlin

The efficiency of mysql subquery union and in, and the efficiency of mysqlin

A problem was found in recent product tests. When the number of concurrent jobs is less than 10, the response time can be kept within 100 milliseconds. However, when the number of concurrencies reaches 30, the response time exceeds 1 second. This is too unacceptable and requires 100 concurrent requests in one second.

After detection, it is found that the time is mainly consumed in one of the storage processes. The statement of the stored procedure has not been found to be unreasonable once. Because mysql itself does not provide a millisecond-level time, google's mysql provides a millisecond-level time function, and then tests and positions it. One of the statements is found to look like this:

Select... from A, B where... and A. id in (select id from C where ...);

The in subquery statements have few results. There is no problem with the explain statement. There is no problem with a single test. However, when the concurrency is large, the problem arises.

A change was made:

Select... from A, B, (select... from C where...) S where ....;

After testing, the performance is greatly improved. The response speed of 50 concurrency and 10 concurrency is almost the same.

In mysql, we recommend that you use subqueries instead of in. I have not performed any experiments before. Now we can see the efficiency. Of course, you cannot believe it. You still have to use your own applications. It is best to do some tests and experiments.

Summary

The above is all about the efficiency of mysql subquery union and in. I hope it will be helpful to you.

If you are interested, refer:

Several important MySQL Variables

MySQL master database binlog (master-log) and slave database relay-log relationship code explanation

Mysql database development specifications [recommended]

If you have any questions, please leave a message for discussion. I also hope that my friends can provide more support to the website of the customer's home!