CODEWITHSUNDEEP
phpmysqloptimizationmysqliquery-optimization
aiswarya krishna
aiswarya krishna

Reputation: 31

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 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?

Upvotes: 0

Views: 226

Answers (3)

Ulrik
Ulrik

Reputation: 513

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.

Upvotes: 0

Your Common Sense
Your Common Sense

Reputation: 157828

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.

Upvotes: 1

Oswald
Oswald

Reputation: 31637

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.

Upvotes: 2

Related Questions