Mysqli fetch_assoc () and loading the Mysqli fetch_all () database?

Question

Strictly from the point of view of MySQL (database performance, not PHP performance) what is the difference between the Mysqli fetch_assoc () and Mysqli fetch_all () loops when receiving query results?

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

In other words, does each additional iteration fetch_assoc () or fetch_array (MYSQLI_NUM) result in more MySQL messages or is the result of the whole query that is already pulled from MySQL at a time?

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

To emphasize, my only concern is that MySQL hears and responds if there is any difference. This is not a question of PHP performance, why is one way better than another, etc. Also, this is not a PDO issue http://php.net/manual/en/mysqli-result.fetch-all.php

Answer 1

Mysqli_fetch_assoc () retrieves one row from code reading.

While mysqli_fetch_all () calls mysqlnd_fetch_all (), which uses a loop to retrieve one row at a time until all rows are retrieved, then it will exit the loop.

Here is the corresponding 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 a “fetch”. PHP extensions either retrieve a single row or retrieve one row at a time until all rows in the result set are retrieved.

Answer 2

Strictly from the point of view of MySQL (database performance, not PHP performance), neither Mysqli fetch_assoc () nor Mysqli fetch_all () have any meaning.

Strictly in terms of overall performance, there is no difference. You can use anything that suits you best in terms of application design, sensitivity, and readability .