mysqli_fetch_array() or mysqli_fetch_all()?

Question

I see comparisons between mysqli_fetch_array() and mysqli_fetch_all() that say that with mysqli_fetch_all() it will take more memory and I will have to iterate over the array.

mysqli_fetch_all() is one call to mysqli but mysqli_fetch_array() is one call per row, for example 100 calls.

I don't know how the mysqli processing works: is calling mysqli_fetch_array() really more efficient when you also take the number of calls into account?

(I already understand that the returned data can be associative arrays or not)

Answer 1

It has nothing to do with whatever efficiency. It's all about usability only.

fetch_all() is a thing that is called a "syntax sugar" - a shorthand to automate a frequently performed operation. It can be easily implemented as a userland function:

function mysqli_fetch_all ($resouce, $mode = MYSQLI_BOTH)
{
    $ret = [];
    while ($row = $resource->fetch_array($mode))
    {
        $ret[] = $row;
    }
    return $ret;
}

Thus you can tell use cases for these functions:

• fetch_all() have to be used if you need an array, consists of all the returned rows, that will be used elsewhere.
• fetch_assoc() in a loop have to be used if you're going to process all the rows one by one right in place.

As simple as that.

These functions bear different purpose and thus there is no point in comparing them.

Note that PDO is tenfold more sweet in terms of syntax sugar, as it's fetchAll() function can return data in dozens different formats

Answer 2

From PHP's page on mysql_fetch_all():

I tested using fetch_all versus while / fetch_array and:

fetch_all uses less memory (but not for so much).

In my case (test1 and test2): 147008,262848 bytes (fetch_all) versus 147112,262888 bytes (fetch_array & while).

So, about the memory, in both cases are the same.

However, about the performance:

My test takes :350ms (worst case) using fetch_all, while it takes 464ms (worst case) using fetch_array, or about 35% worst using fetch_array and a while cycle.

So, using fetch_all, for a normal code that returns a moderate amount of information is:

a. cleaner (a single line of code)
b. uses less memory (about 0.01% less)
c. faster.

php 5.6 32bits, windows 8.1 64bits