5

I am seriously doubting mysqli's multi-queries are truly multi-queries in the sense that the total trip made to the database from the web server is only 1.

If we call a 5-statement multi-query, we have to do multi_query() once and next_result() 4 times.

So isn't that still 5 trips to the database from the web server?

And besides, mysqli_use_result(), mysqli_more_results() and mysqli_store_result() all requires one trip to the database per call?

Dharman
  • 30,962
  • 25
  • 85
  • 135
Pacerier
  • 86,231
  • 106
  • 366
  • 634
  • Good question. I suspect the queries will be sent together, and the results will be buffered in reasonably-sized chunks under the hood. The connection itself should be persistent to save overhead. I look forward to some expert answers, though. – grossvogel Aug 10 '11 at 00:08

2 Answers2

1

Yes, mysqli:multi_query() does send the SQL in one go to the server. The function will also issue a single next_result() call, which means that it will wait for the first result set to be returned. The processing of the SQL will continue on the MySQL server while PHP is free to do something else.

However, to get the results you need to block PHP script and ask MySQL to provide the result. This is why you need to call next_result() 4 times if you expect to receive 5 results. Each call does make a new trip to the server to ask for the results, which might already be ready and waiting to be fetched or not.

In practice, you might end up making more than 5 trips to the MySQL server. It's worth pointing out that this is not going to improve the performance of your PHP script on its own. Unless you have some very good reason to execute SQL in bulk on the MySQL server do not use mysqli::multi_query(). Stick to using prepared statements.

Dharman
  • 30,962
  • 25
  • 85
  • 135
0

Yes. mysqli::multi_query does send multiple queries in the same MySQL packet.

  • so do you mean that `mysqli_use_result()`, `mysqli_next_result().`, `mysqli_more_results()` and `mysqli_store_result()` all is done in the web server side and does not require any trips to the database? – Pacerier Jul 24 '11 at 19:17
  • I'm not sure. If it's important to know, you can trace your PHP script using `strace` and see for yourself. –  Jul 24 '11 at 20:05
  • Fetching the next result does not happen asynchronously, only processing. Fetching the results happens on a synchronous basis. So one trip runs all five queries. But you still need four more trips to get the rest of the results. That can be faster if you don't care about the results. But if you want to do any more work on that database connection, you will need the additional fetches. And because you are engaging asynchronously, it can actually be slower to get the subsequent results (perhaps the DB server pages out before you request them). – mdfst13 Jul 19 '22 at 01:14