A brief discussion on the efficiency of MySQL subquery union and in

Recent product testing found a problem that when the number of concurrent calls is less than 10, the response time can be maintained within 100 milliseconds. However, when the number of concurrent requests reaches 30, the response time exceeds 1 second. This is unacceptable, the requirement is to run 100 concurrent requests in 1 second.

After testing, it was found that the time was mainly consumed in one of the storage processes. I went through the statements of the stored procedure one by one and found no obvious unreasonableness. Because MySQL itself cannot provide millisecond-level time, I googled a MySQL time function that can provide milliseconds, did some testing, and made a positioning. It was found that it was one of the statements, which looked like this:

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

The in subquery statement produces very few results, just a few. There is no problem explaining this statement. There is no problem with single test. But problems arise when the number of concurrent requests is large.

Made a change:

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

After another test, the performance has improved significantly. The response speed of 50 concurrency and 10 concurrency is almost the same.

In MySQL, MySQL may suggest changing to using subquery union instead of in. It has not been experimented with before. Now I finally see the efficiency. Of course, we can't believe it all. It still depends on your own application. It would be best to do some testing and experimenting.

Summarize

The above is all the content of this article about the efficiency of MySQL subquery union and IN. I hope it will be helpful to everyone.

Interested friends can refer to:

Several important MySQL variables

Detailed explanation of the code between the MySQL master library binlog (master-log) and the slave library relay-log

MySQL database development specifications [recommended]

If you have any questions, please leave a message and we can discuss together. I also hope that friends will support the 123WORDPRESS.COM website!