aiswarya krishna aiswarya krishna - 1 month ago 8
MySQL Question

is it better to traverse through a PHP array than traversing through mysqli associative array?

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 reults 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? Whihc us more optimized?

Answer

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 queries to the database inside the loop is what is going to take time here.