mysqli multi_query followed by query

Question

I am currently doing the following:

$mysqli = new mysqli($server, $username, $password, $database);

$mysqli->multi_query($multiUpdates);
while ($mysqli->next_result()) {;} // Flushing results of multi_queries
$mysqli->query($sqlInserts);

Is there a faster way to dump the results?

I do not need them and just want to run the next query however I get the error:

Commands out of sync; you can't run this command now

Problem is the while ($mysqli->next_result()) {;} takes about 2 seconds which is a waste for something I don't want.

Any better solutions out there?

Answer 1

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.

Answer 2

If closing and reopening the connection works for you, then you might be better off changing the order:

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

If you don't care which runs first, the query runs more predictably. As soon as it finishes, MySQL will return the results of the insert statement to the PHP client (probably mysqlnd). The connection will then be clear and can accept the next request. No need to close and reopen after a query. So you save the time it takes to close and reopen the connection with this order.

The multi_query is more complicated. It returns the results of the first update before the PHP code continues. At this point, we don't know if the later updates have run or not. The database won't accept any more queries on this connection until it has finished with the multi_query, including passing the results back with next_result. So what happens when you close the query?

One possibility is that it blocks until the multi_query is finished but does not require the results. So closing the connection essentially skips the part where the database server returns the results but still has to wait for them to be generated. Another possibility is that the connection closes immediately and the database continues with the query (this is definitely what happens if the connection is simply dropped without formally closing it, as the database server won't know that the connection is broken until it finishes or times out, see here or here).

You'll sometimes see the claim that query and multi_query take the same time. This is not true. Under some circumstances, multi_query can be slower. Note that with a normal query (using the default MYSQLI_STORE_RESULT), the database can simply return the result as soon as it finishes. But with multi_query (or with MYSQLI_USE_RESULT), it has to retain the result on the database server. If the database server stores the result, it may have to page it out of memory or it may deliberately store the result on disk. Either way, it frees up the memory but puts the result in a state where it takes more time to access (because disk is slower than memory).

NOTE for other readers: multi_query is harder to use safely than query. If you don't really know what you are doing, you are probably better off using PDO than mysqli (because PDO does more of the work for you) and you are almost certainly better off doing your queries one at a time. You should only use multi_query if you understand why it increases the risk of SQL injections and are avoiding it. Further, one usually doesn't need it.

The only real advantage to multi_query is it allows you to do your queries in one block. If you already have queries in a block (e.g. from a database backup), this might make sense. But it generally doesn't make sense to aggregate separate queries into a block so as to use multi_query. It might make more sense to use INSERT ON DUPLICATE KEY UPDATE to update multiple rows in one statement. Of course, that trick won't work unless your updates have a unique key. But if you do, you might be able to combine both the inserts and the updates into a single statement that you can run via query.

If you really need more speed, consider using something other than PHP. PHP produces HTML in response to web requests. But if you don't need HTML/web requests and just want to manipulate a database, any shell language will likely be more performant. And certainly multithreaded languages with connection pools will give you more options.