Is MySQLi_Multi_Query Asynchronous

Is mysqli_multi_query asynchronous?

mysqli_query supports async queries. See changelog on mysqli_query. mysqli_multi_query does not mention async on the manual page specifically. Only thing mysqli_multi_query does is tell MySQL to execute a bulk set of queries. It's up to PHP to wait for the results.

As your code stands, your sending a bulk set of SQL statements to MySQL and not waiting for any results. Only time your mysqli_multi_query will ever die is when the first statement fails. So, that function returns true immediately after the first statement and moves on to the next line. That's why the queries are executing after the PHP is finished. MySQL is still working. PHP has moved on.

It's best that you loop through the results of each statement before moving on with your code. The following will die if a query fails anywhere in your batch.

mysqli_multi_query($db_link, file_get_contents($path.'/'.$file)) or die ("$database 4" .mysqli_error($db_link)); 

do {
    if($result = mysqli_store_result($db_link)){
        mysqli_free_result($result);
    }
} while(mysqli_next_result($db_link));

if(mysqli_error($db_link)) {
    die(mysqli_error($db_link));
}

Speed/best practice flushing mysqli_multi_query()

It is not next_result() to blame but queries themselves. The time your code takes to run relies on the time actual queries take to perform.

Although mysqli_multi_query() returns control quite fast, it doesn't mean that all queries got executed by that time. Quite contrary, by the time mysqli_multi_query() finished, only first query got executed. While all other queries are queued on the mysql side for the asynchronous execution.

From this you may conclude that next_result() call doesn't add any timeout by itself - it's just waiting for the next query to finish. And if query itself takes time, then next_result() have to wait as well.

Knowing that you already may tell which way to choose: if you don't care for the results, you may just close the connection. But in fact, it'll be just sweeping dirt under the rug, leaving all the slow queries in place. So, it's better to keep next_result() loop in place (especially because you have to check for errors/affected rows/etc. anyway) but speed up the queries themselves.

So, it turns out that to solve the problem with next_result() you have to actually solve the regular problem of the query speed. So, here are some recommendations:

1. For the select queries it's usual indexing/explain analyze, already explained in other answers.
2. For the DML queries, especially run in batches, there are other ways:

Speaking of Craig's case, it's quite much resembling the known problem of speed of innodb writes. By default, innodb engine is set up into very cautious mode, where no following write is performed until engine ensured that previous one were finished successfully. So, it makes writes awfully slow (something like only 10 queries/sec). The common workaround for this is to make all the writes at once. For insert queries there are plenty of methods:

• you can use multiple values insert syntax
• you can use LOAD DATA INFILE query
• you can wrap all the queries in a transaction.

While for updating and deleting only transaction remains reliable way. So, as a universal solution such a workaround can be offered

 $multiSQL = "BEGIN;{$multiSQL}COMMIT;";
 $mysqli->multi_query($multiSQL);
 while ($mysqli->next_result()) {/* check results here */}

If it doesn't work/inapplicable in your case, then I'd suggest to change mysqli_multi_query() for the single queries run in a loop, investigate and optimize the speed and then return to multi_query.

Is mysqli::multi_query more efficient than several single queries?

• What is multi_query doing under the hood? - Just sending all the queries to the server at once instead of one at a time, and retrieving all the results in one go. Nothing more complicated than that.

• Does multi_query simply hit the server x number of times and aggregates the results? - It "hits" the server twice - once to send the queries and once to retrieve the results.

• Is there a case where single queries may be more efficient than multiple queries? - depends how you define "efficient". multi_query() is light on the network but memory heavy, running query() in a loop vice versa.

  For many SELECT statements that return large result sets the loss in memory consumption is likely to vastly outweigh the gain in terms of the network and most of the time you'd be better to issue the queries and process the result sets one at a time - although this depends on exactly what you are doing with the data. But if you needed to run many UPDATE statements, it is likely that multi_query() would be better since the return values are just success/fail and the memory consumption will be light.

  You would have to weigh up all the factors like what you are doing, how long you expect it to take, the network latency between the (database) server and client, the available resources (mostly memory) on the server and the client, etc, etc... and take it on a case by case basis.

I found this record of some performance testing done a while ago, where the conclusion is that there is an overall efficiency gain to be found from using multi_query(). However the test case was simply running 4 queries, each one SELECTing a single result, and the definition of "more efficient" is simply "faster". There is no testing for larger numbers of queries or larger result sets and, while speed is important, it is not the be-all and end-all - I can make anything run incredibly fast if I give it an unlimited amount of memory, but an attempt to do anything concurrently will fail miserably. It is also not a real world test, since the end result could be achieved with a single JOINed query. It does make for some interesting read though.

Personally I feel this is somewhat academic because if you are running a large set of statements at once, 90% of the time they will vary only in the data that is being passed and the query structure will remain the same - which is an obvious candidate for prepared statements.

mysqli multi_query followed by query

Found a faster solution which saves about 2-3 seconds when updating 500 records and inserting 500 records.

function newSQL() {
    global $server, $username, $password, $database;
    $con = new mysqli($server, $username, $password, $database);
    return $con;
}

$mysqli = newSQL();
$mysqli->multi_query($multiUpdates);
$mysqli->close();

$mysqli = newSQL();
$mysqli->query($sqlInserts);
$mysqli->close();

Not sure how practical it is but works well for speed.

How to use async Mysql query with PHP PDO

No. You cannot use Mysql async queries with PDO. Mysqli is the only choice.

You can use for this either mysqli_multi_query or the regular query/poll/reap sequence.

---

Related Topics