Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PHP: how are query results stored in mysqli_result

Tags:

php

memory

mysqli

When I made a query to the database and retrieve the results in mysqli_result, the memory usage is extremely small. However, when I fetch all the rows in the query results in to an associative array, the memory usage becomes extremely high.

<?php
    require_once("../config.php"); //db connection config
    $db = new mysqli(DB_HOST,DB_USER,DB_PASSWORD,DB_DBASE);

    $query ="select * from table_name";
    if($r = $db->query($query)){
    echo "MEMORY USAGE before :  ". memory_get_usage()."<br><br>";
    $rows = array();
    while($row = $r->fetch_assoc()){

        $rows[]= $row;
    }
    echo "MEMORY USAGE after :  ". memory_get_usage()."<br><br>";


    //before: 660880
    //after:  114655768
    // # of records: around 30 thousands
?>

It makes sense to me that storing this many results is very memory consuming, but I'm just wondering how come mysqli_result is so small. It can't be that the results are queried to the dbase every time fetch_assoc is called. So then where are the results stored in the memory.

like image 270
spchuang Avatar asked Jul 29 '12 03:07

spchuang


People also ask

What does mysqli_result return?

Fetches one row of data from the result set and returns it as an enumerated array, where each column is stored in an array offset starting from 0 (zero). Each subsequent call to this function will return the next row within the result set, or null if there are no more rows.

What is the output of mysqli_query in PHP?

Mysqli_connect doesn't target the table and mysqli_query doesn't output anything.

What does Mysqli_num_rows return?

The mysqli_num_rows() function returns the number of rows in a result set.

What does Mysqli_connect return?

The mysqli_connect() function establishes a connection with MySQL server and returns the connection as an object.


2 Answers

There is a HUGE difference between fetching results and storing a pointer to a resource.

If you echo $r; before your first call to memory_get_usage();, you will realize it is just a pointer. This is the pointer to your result set. Until you fetch your results, the result set will not actually be stored into memory.

I would suggest that you run fetchAll() for what you are trying to do. This will then result in 1 method accessing all your results with better performance since it's pawned off on the mysqli extension (C Library) rather than a loop in PHP.

You can also use the free results function to clear your results from memory when you are done with them. This is like closing a cursor in Java if you are familiar.

like image 52
Mike Mackintosh Avatar answered Oct 05 '22 06:10

Mike Mackintosh


I think you should to this instead:

while($row = $r->fetch_assoc()){
   //Do whatever you need with the record, then:
   unset($row);
}

The way you posted is gathering a huge array in $rows, and memory usage reflects that.

like image 24
Niloct Avatar answered Oct 05 '22 05:10

Niloct