Does mysqli_multi_query send the whole SQL to the database in a single trip?

Question

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?

Answer 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.