Decreasing PHP script memory

PHP
1

Hi all,

I have a 17 megabyte database with 40,000+ rows that I run with a script called update.php. This script that queries the database uses 108 megabytes of memory just to pull all database rows, and 111 megabytes total for the entire script. I recently increased my memory_limit to 150MB.

Can you look at this code and tell me if there is a way to optimize it to decrease memory usage? The following function accounts for 108 MB of memory.

function PullDatabase($database) {
	$result = mysql_query("SELECT * FROM $database") or die(mysql_error());
	$i = 0;
	while($row = mysql_fetch_assoc($result)){
		$data[$i] = $row;
		$data[$i][recorded_at] = UnixTime($row[recorded_at]);
		$i++;
	}
	return $data;
}
$data = PullDatabase('dbname');

Is there anyway to optimize this function while giving the same output?

Once the database is pulled in my script, I run it through about 15 functions to pull data from it. I figured it would be less intensive if I pulled all the rows/columns once, and used the $data array in all my functions (even if the function does not require all the selected rows).

Would it be better to query the database individually for each function, calling only the required columns?

Thanks
Brandon

2

Option 1: Add a parameter to the function to limit the SELECT to just what you need.
Option 2: Do away with the function all together and add SELECT/Fetch loops wherever needed, processing the rows inside the loop. That way you’re not filling up a huge array.

3

Do you really need all the fields from all the rows your returning with the query? if you don’t then specify the ones that you want in the SELECT clause.

4

How much memory does this use:


function PullDatabase($table) {
    $data = array();
    $result = mysql_query("SELECT *, TO_UNIXTIME(recorded_at) as recorded_at FROM $table") or die(mysql_error());
    while($row = mysql_fetch_assoc($result)){
        $data[] = $row;
    }
    return $data;
}
$data = PullDatabase('table_name');

Also, how are you using your $data? Since the way you have it laid out, you can’t do anything meaningful with it, unless you for loop over it every time you need something.

5

Selecting all fields is going to use memory, like everyone else said above.

One bit to note: you select from a TABLE, not a database. A database contains many tables. A table contains many rows.