Why PHP Mysql multi_query() is slower than loop query()

Question

I'm working on HHVM with MySQL. And I'm really confused to find out that batching 2000 sql queries using multi_query() is much slower than a 2000 loop with single query() (please see the code and the result at end). By further profiling, I found the API next_result() takes most of the time (~70%).

My questions are:
(1) Why the API next_result() is so slow?
(2) If I want to do thousands of sql queries together, is there any way better than a naive loop?
Thanks!

Here is the code (php):

$conn = new mysqli("localhost", "root", "pwd", "table");

$loop = 2000;
$q = "select * from ContactInfo;";

// single query in a loop
$results = array();
$sq_start = microtime(true);
for ($i=0; $i < $loop; $i++) {
  $ret = $conn->query($q);
  $results[] = $ret;
}
for ($i=0; $i < $loop; $i++) {
  $xx = $results[$i]->fetch_all();
}
$sq_end = microtime(true);

// construct the multi-query
for ($i=0; $i < $loop; $i++) {
  $m_q .= $q; 
}

// multi-query in one round-trip
$mq_start = microtime(true);
$conn->multi_query($m_q);
do {
  $ret = $conn->store_result();
  $xx = $ret->fetch_all();
} while($conn->next_result());
$mq_end = microtime(true);

echo "Single query: " . ($sq_end - $sq_start)*1000 . " ms\n";
echo "Multi query: " . ($mq_end - $mq_start)*1000 . " ms\n";

The result is following:

Single query: 526.38602256775 ms
Multi query: 1408.7419509888 ms

Note: next_result() will consume 922ms in this case.

If you're doing thousands of the same queries (with only the parameters changed, if any), you'll get the best performance using prepared statements from PHP's [`PDO`](http://php.net/manual/en/book.pdo.php) library — Matthew Herbst, Feb 21 '16 at 02:36
@MatthewHerbst so you gonna say than with PHP's mysqli library prepared statements aren't that fast? — Your Common Sense, Feb 21 '16 at 08:04
@MatthewHerbst Thanks for your advise, I have just tried mysqli::prepare() API, will try PDO later. Share some feelings: it's really hard (if not impossible) to get the wanted result in HHVM using prepare() for the following reasons: (1) HHVM do not support mysqli_stmt::get_result() API (2) For "select * ..." query, it's hard to use mysqli_stmt::bind_result() API. — naizheng TAN, Feb 21 '16 at 14:13
Yeah, give PDO a try if you can't get mysqli to work, though PDO will force you to learn a slightly new style of doing things since there is no procedural style for PDO, only object-oriented. In terms of your troubles, make sure that you're calling `bind_result()` "after `mysqli_stmt_execute()` and prior to calling `mysqli_stmt_fetch()`" — Matthew Herbst, Feb 21 '16 at 18:14

score 0 · Answer 1 · edited May 23 '17 at 12:07

0

The simplest answer here is overhead. multi_query() builds a data object for each result set. It then has to store that object over and over.

By contrast, you're running the same query over and over but only writing a simple data array to store the results. PHP can then free the memory of the previous result set because you're constantly overwriting the same variable (once a data object has nothing pointing to it internally it can be garbage collected).

This isn't a very good use case, though. There's no real world application here because there's no need to run the same query over and over (your DB should cache the results the first run and then store the data in memory to faster retrieval). For a single result set, the difference here is negligible.

edited May 23 '17 at 12:07

Community

1
1

answered Feb 21 '16 at 02:47

Machavity

30,841
27
92
100

Thanks for your answer. But it's pretty suspicious that even "copy over and over" will cost 900ms. Memory copy is super fast. I have checked HHVM's source code, and confirmed that HHVM does nothing but calls MySQL's C API mysql_next_result(). – naizheng TAN Feb 21 '16 at 14:11
Yes... but you're storing an **object** over and over vs storing an **array** over and over. And yes, memory copy is fast but you then have to process each object over and over. There's going to be some overhead involved – Machavity Feb 21 '16 at 17:20

Why PHP Mysql multi_query() is slower than loop query()

1 Answers1