Is it better to traverse through a PHP array than traversing through MySQLi associative array?

Question

I have the following code in which I am using a loop inside a loop to create a PHP array:

$result= mysqli_query($myqueri1);
    while($row = mysqli_fetch_assoc($result)) 

    { 

     $result1=mysqli_query($myqueri2); //This query utilizes a variable obtained from the outer loop. So this cant be written outside the loop.
     while($row = mysqli_fetch_assoc($result)) 
     {

     //Operations
     }

    }

The two arrays contain almost 50k rows. And they are indexed.

However, I am more concerned about the optimization issue. If I store all the results of loop 1 into a PHP array and then iterate through it, does it make any difference? Or is using while($row = mysqli_fetch_assoc($result)) same as going through a common PHP loop? Which is more optimized?

It is unclear why you need a php loop. But your example does not explain why you would do that. Performance wise consider a join. Assume that the mysql or mariadb developers did a fine job in C or even assembly, probably better than you possibly can in PHP. A single query generating even redundant data will have a better performance. — theking2, Oct 27 '21 at 18:25

Oswald · Answer 1 · 2016-10-22T07:52:51.047

Database access is slow compared to accessing elements of an array. But if the array comes from a database access, there should not be much difference.

I doubt your claim "So this cant be written outside the loop." For example

for ($i = 0; $i < 3, ++$i) {
  mysqli_query("SELECT foo FROM bar WHERE id = $i");
}

can be written as

mysqli_query("SELECT foo FROM bar WHERE id IN (0, 1, 2)");

As with all optimizations: before you optimize, find out where the bottleneck is. After optimization, confirm that the bottleneck is actually gone.

score 1 · Answer 2 · answered Oct 22 '16 at 08:11

If I store all the reults of loop 1 into a PHP array and then iterate through it,

...then you will iterate twice

Whihc is more optimized?

Running a loop once is more optimized than running a loop twice. So it makes your question is rather on the arithmetic, not optimization.

However, I am more concerned about the optimization issue.

Then you have to use JOIN in your first query and get all the data without a nested query at all.

score 0 · Accepted Answer · edited May 23 '17 at 12:19

If you are using mysqli_query() with default flags MYSQLI_STORE_RESULT you already transfer the whole result set from the database into your application. See http://php.net/manual/en/mysqli.query.php specifically the result_mode flag, and mysqli_store_result() vs. mysqli_use_result() for a better explanation of what the flags do.

This means that the outer loop in your case shouldn't differ much in performance if you first populate a php array with it or just keep doing what you are doing now.

The big performance difference you can make here is if it is at all possible to combine the second, inner loop querys into some kind of join into the first query.

Sending multiple queries to the database inside the loop is what is going to take time here.

Is it better to traverse through a PHP array than traversing through MySQLi associative array?

3 Answers3