Is mysqli :: multi_query more efficient than multiple separate queries?

Question

Someone picked up the multi_query MySQLi function in the answer, arguing that it would be better than looping through 3 separate queries. I tried to answer some kind of Google answer, but actually didn’t get anything that really satisfied my curiosity, so I hope you guys can better understand the reasons for using it, rather than save a few lines of code.

So here is what interests me:

• What does multi_query do under the hood?
• Does multi_query use x on the server x times and aggregate the results?
• Is there a case where single queries can be more efficient than multiple queries?

I know that getting the database 3 times per million elements and breaking them into one huge object is not suitable for memory use, but I know that there is a reason for it to exist, and I am also sure that it should be avoided there. I hope to better understand this so that I can put this in my bag of tricks when the need arises.

Thank you for your time!

Answer 1

• What is multi_query doing under the hood? - simply sending all requests to the server at the same time, and not one at a time, and receiving all the results at a time. Nothing more complicated.

• Does multi_query simply hit the server x number of times and aggregates the results? - He "hits" the server twice - once for sending requests and once for receiving results.

• Is there a case where single queries may be more efficient than multiple queries? - Depends on how you define "effective." multi_query() glows on the network, but the memory is heavy, query() works in a loop the other way around.

  For many SELECT that return large result sets, memory loss is likely to significantly outweigh the gain in network terms, and most of the time you will be better off issuing queries and processing the result sets one at a time, although it depends on what you do with data. But if you need to run many UPDATE , it is likely that multi_query() will be better, as the return values ​​will simply be successful / unsuccessful and the memory consumption will be bright.

  You will have to weigh all factors, such as what you do, how much time you expect from it, latency of the network between the server (client) and the client, available resources (mainly memory) on the server and client, etc. etc .... and take it on a case-by-case basis.

I found this report on some performance tests , made some time ago, when the conclusion is that the overall performance gain can be found from using multi_query() . However, the test script just ran 4 queries, each of which is SELECT in one result, and the definition of “more efficient” is simply “faster”. There is no testing for more queries or large result sets, and although speed is important, this is not all and all - I can do something incredibly fast if I give it an unlimited amount of memory, but trying to do something at the same time will fail. It is also not a real world test, as the end result can be achieved with a single JOIN ed query. It really makes some interesting reads, though.

Personally, I think this is quite academic, because if you run a large set of operators at the same time, 90% of the time they will change only in the transmitted data, and the structure of the requests will remain the same - an obvious candidate for prepared statements.

Answer 2

The answer above is incorrect.

What does multi_query do under the hood?

It simply sends all requests to the server at a time, receiving the result only for the first. Thus, in reality, multi_query is a kind of wrapper for executing an asynchronous query.

Does multi_query use x on the server x times and aggregate the results?

multi_query removes the server only once. All other hits that you must do manually by calling next_result to get all the other requests.

Is there a case where single queries can be more efficient than multiple queries?

this question suggests that there is a case where multi_query is faster. This is a dubious statement. For a regular request, network latency is an insignificant part of the total execution time. If you are really worried about speed with such a difference, take a look at HandlerSocket - it will flash quickly. However, for regular development, such a difference between one and several requests would be your least concern. If you care about real life, not imaginary.