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

Question

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

score 12 · Accepted Answer · answered Sep 30 '13 at 21:16

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.

score -3 · Answer 2 · answered Sep 29 '13 at 20:05

-3

Strictly from MySQL's point of view (database performance, not PHP performance) neither Mysqli fetch_assoc() nor Mysqli fetch_all() has any significance.

Strictly from general performance point of view, there is not a slightest difference. You can use anything that suits you more from application design, sensibility and readability point of view.

answered Sep 29 '13 at 20:05

Your Common Sense

156,878
40
214
345

I'm not asking about significance. Both methods must communicate with MySQL. If I were to monitor MySQL for both methods, would the communication be identical for both methods? If the answer is yes, that is significant. If the answer is no, that is significant too. Why? Because the "fetch_all" implies you are getting the entire result from MySQL all at once, instead of in stages. If you're saying fetch_assoc() and fetch_array() always fetch the entire result ("all" of it) I'd say that's important to know. If the entire result is already fetched either way, that's significant too. – PJ Brunet Sep 30 '13 at 07:48
11

How I **better** spend my free time is none of your business. Asking how Mysqli works behind the scenes is perfectly reasonable. If you disagree, that's your problem. Frankly, I find your dismissive attitude rude. – PJ Brunet Sep 30 '13 at 08:06

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

2 Answers2