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
  • 21,838
  • 18
  • 57
  • 107
Pacerier
  • 76,400
  • 86
  • 326
  • 602
  • 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

0

Yes, mysqli:multi_query() does send the SQL in one go to the server. This operation is asynchronous. PHP does not wait for MySQL to perform the full SQL. As soon as mysqli gets a single result, the control is given back to PHP script while the execution of the SQL continues on the MySQL server.

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
  • 21,838
  • 18
  • 57
  • 107
0

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

duskwuff -inactive-
  • 171,163
  • 27
  • 219
  • 269
  • 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. – duskwuff -inactive- Jul 24 '11 at 20:05