Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysqli fetch_assoc() loop vs. Mysqli fetch_all() database load?

Tags:

php

mysql

mysqli

Strictly from MySQL's point of view (database performance, not PHP performance) what's the difference between a Mysqli fetch_assoc() loop vs. Mysqli fetch_all() when retrieving query results?

Let's say for $result = $qdb->query("SELECT name, id FROM cats");

In other words, does each additional fetch_assoc() or fetch_array(MYSQLI_NUM) iteration result in more MySQL communication or is the entire query result already pulled from MySQL at one time?

In other words, can Mysqli fetch_all() make life easier for MySQL?

To emphasize, I'm only concerned with what MySQL hears and responds with, if there's any difference. This is not a question about PHP performance, why one way is better than the other, etc. Also, this is not a PDO question http://php.net/manual/en/mysqli-result.fetch-all.php

like image 201
PJ Brunet Avatar asked Sep 29 '13 19:09

PJ Brunet


1 Answers

From reading the code, mysqli_fetch_assoc() fetches one row.

Whereas mysqli_fetch_all() calls mysqlnd_fetch_all(), which use a loop to fetch one row at a time until all rows have been fetched, then it breaks out of the loop.

Here's the relevant function in mysqlnd, edited for length:

MYSQLND_METHOD(mysqlnd_res, fetch_all)(MYSQLND_RES * result, unsigned int flags, zval *return_value TSRMLS_DC ZEND_FILE_LINE_DC)
{
    ...
    do {
            MAKE_STD_ZVAL(row);
            mysqlnd_fetch_into(result, flags, row, MYSQLND_MYSQLI);
            if (Z_TYPE_P(row) != IS_ARRAY) {
                    zval_ptr_dtor(&row);
                    break;
            }
            add_index_zval(return_value, i++, row);
    } while (1);
    ...
}

So the answer is: from the point of view of the MySQL server, there is no such thing as "fetch all." The PHP extensions either fetch one row, or else fetch one row at a time until all the rows in the result set have been fetched.

like image 160
Bill Karwin Avatar answered Oct 18 '22 23:10

Bill Karwin