Close MySQL Result

I think I am a dork.

How do I close a MySQL result set to free memory?

Given something like this:

$gsql = "Select * from resources where queryName = 'Production';";

$myresult = mysql_query($gsql) or die('Cannot execute Query: ' . mysql_error());

$Row = mysql_fetch_assoc($myresult);

if ($Row == true) { $_SESSION['PRODUCTION'] = $Row['queryValue']; }
else { $_SESSION['PRODUCTION'] = "TRUE"; }

How do I free up $myresult and $Row?

-ML

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Close MySQL Result

http://www.php.net/manual/en/function.mysql-free-result.php

mysql_free_result($myresult);

NOTE: mysql_free_result() only needs to be called if you are concerned
about how much memory is being used for queries that return large result
sets. All associated result memory is automatically freed at the end of the
script's execution.

http://us2.php.net/manual/en/function.unset.php

unset($Row);

> -----Original Message-----
> From: Slack-Moehrle [mailto:mailinglists@mailnewsrss.com]
> Sent: Wednesday, January 20, 2010 1:21 PM
> To: php-general@lists.php.net
> Subject: [PHP] Close MySQL Result
>
> I think I am a dork.
>
> How do I close a MySQL result set to free memory?
>
> Given something like this:
>
> $gsql = "Select * from resources where queryName = 'Production';";
>
> $myresult = mysql_query($gsql) or die('Cannot execute Query:
> ' . mysql_error());
>
> $Row = mysql_fetch_assoc($myresult);
>
> if ($Row == true) { $_SESSION['PRODUCTION'] = $Row['queryValue']; }
> else { $_SESSION['PRODUCTION'] = "TRUE"; }
>
> How do I free up $myresult and $Row?
>
> -ML
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Close MySQL Result

Daevid,

Thanks for the links so I can read up!

-ML

----- Original Message -----
From: "Daevid Vincent"
To: php-general@lists.php.net
Cc: "Slack-Moehrle"
Sent: Wednesday, January 20, 2010 1:24:16 PM
Subject: RE: [PHP] Close MySQL Result

http://www.php.net/manual/en/function.mysql-free-result.php

mysql_free_result($myresult);

NOTE: mysql_free_result() only needs to be called if you are concerned
about how much memory is being used for queries that return large result
sets. All associated result memory is automatically freed at the end of the
script's execution.

http://us2.php.net/manual/en/function.unset.php

unset($Row);

> -----Original Message-----
> From: Slack-Moehrle [mailto:mailinglists@mailnewsrss.com]
> Sent: Wednesday, January 20, 2010 1:21 PM
> To: php-general@lists.php.net
> Subject: [PHP] Close MySQL Result
>
> I think I am a dork.
>
> How do I close a MySQL result set to free memory?
>
> Given something like this:
>
> $gsql = "Select * from resources where queryName = 'Production';";
>
> $myresult = mysql_query($gsql) or die('Cannot execute Query:
> ' . mysql_error());
>
> $Row = mysql_fetch_assoc($myresult);
>
> if ($Row == true) { $_SESSION['PRODUCTION'] = $Row['queryValue']; }
> else { $_SESSION['PRODUCTION'] = "TRUE"; }
>
> How do I free up $myresult and $Row?
>
> -ML
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Close MySQL Result

--=-tCy6JTzHswPQDJESXI43
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Wed, 2010-01-20 at 13:24 -0800, Daevid Vincent wrote:

> http://www.php.net/manual/en/function.mysql-free-result.php
>
> mysql_free_result($myresult);
>
> NOTE: mysql_free_result() only needs to be called if you are concerned
> about how much memory is being used for queries that return large result
> sets. All associated result memory is automatically freed at the end of the
> script's execution.
>
> http://us2.php.net/manual/en/function.unset.php
>
> unset($Row);
>
> > -----Original Message-----
> > From: Slack-Moehrle [mailto:mailinglists@mailnewsrss.com]
> > Sent: Wednesday, January 20, 2010 1:21 PM
> > To: php-general@lists.php.net
> > Subject: [PHP] Close MySQL Result
> >
> > I think I am a dork.
> >
> > How do I close a MySQL result set to free memory?
> >
> > Given something like this:
> >
> > $gsql = "Select * from resources where queryName = 'Production';";
> >
> > $myresult = mysql_query($gsql) or die('Cannot execute Query:
> > ' . mysql_error());
> >
> > $Row = mysql_fetch_assoc($myresult);
> >
> > if ($Row == true) { $_SESSION['PRODUCTION'] = $Row['queryValue']; }
> > else { $_SESSION['PRODUCTION'] = "TRUE"; }
> >
> > How do I free up $myresult and $Row?
> >
> > -ML
> >
> > --
> > PHP General Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
>
>


And you can unset() $row if you want to free memory used by that
variable, although it should be noted that once unset, it's up to PHP's
garbage collection to free the memory, but using unset() on the variable
tells PHP that it can free it if necessary.

If you're that worried about memory as well, try to optimise your
queries a little bit. For example, instead of retrieving all the results
in a table and using PHP to output only what you need, use the WHERE and
LIMIT clauses in MySQL to narrow down the results to only those that you
actually need.

Thanks,
Ash
http://www.ashleysheridan.co.uk



--=-tCy6JTzHswPQDJESXI43--

Re: Close MySQL Result

Ashley Sheridan wrote:
> On Wed, 2010-01-20 at 13:24 -0800, Daevid Vincent wrote:
>
>> http://www.php.net/manual/en/function.mysql-free-result.php
>>
>> mysql_free_result($myresult);
>>
>> NOTE: mysql_free_result() only needs to be called if you are concerned
>> about how much memory is being used for queries that return large result
>> sets. All associated result memory is automatically freed at the end of the
>> script's execution.
>>
>> http://us2.php.net/manual/en/function.unset.php
>>
>> unset($Row);
>>
>>> -----Original Message-----
>>> From: Slack-Moehrle [mailto:mailinglists@mailnewsrss.com]
>>> Sent: Wednesday, January 20, 2010 1:21 PM
>>> To: php-general@lists.php.net
>>> Subject: [PHP] Close MySQL Result
>>>
>>> I think I am a dork.
>>>
>>> How do I close a MySQL result set to free memory?
>>>
>>> Given something like this:
>>>
>>> $gsql = "Select * from resources where queryName = 'Production';";
>>>
>>> $myresult = mysql_query($gsql) or die('Cannot execute Query:
>>> ' . mysql_error());
>>>
>>> $Row = mysql_fetch_assoc($myresult);
>>>
>>> if ($Row == true) { $_SESSION['PRODUCTION'] = $Row['queryValue']; }
>>> else { $_SESSION['PRODUCTION'] = "TRUE"; }
>>>
>>> How do I free up $myresult and $Row?
>>>
>>> -ML
>>>
>>> --
>>> PHP General Mailing List (http://www.php.net/)
>>> To unsubscribe, visit: http://www.php.net/unsub.php
>>>
>>
>
>
> And you can unset() $row if you want to free memory used by that
> variable, although it should be noted that once unset, it's up to PHP's
> garbage collection to free the memory, but using unset() on the variable
> tells PHP that it can free it if necessary.
>
> If you're that worried about memory as well, try to optimise your
> queries a little bit. For example, instead of retrieving all the results
> in a table and using PHP to output only what you need, use the WHERE and
> LIMIT clauses in MySQL to narrow down the results to only those that you
> actually need.
>

you'll also find a performance upgrade if you load all sql results in to
an array and close up the query / free the results before working on them.

query
for() {
$results[] = $row;
}
close stuff
work on results


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Close MySQL Result

At 1:20 PM -0800 1/20/10, Slack-Moehrle wrote:
>I think I am a dork.

Aren't we all?

tedd

--
-------
http://sperling.com http://ancientstones.com http://earthstones.com

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Close MySQL Result

tedd wrote:
> At 1:20 PM -0800 1/20/10, Slack-Moehrle wrote:
>> I think I am a dork.
>
> Aren't we all?

Speak for yourself dork!

;)

Cheers,
Rob.
--
http://www.interjinn.com
Application and Templating Framework for PHP

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Close MySQL Result

> -----Original Message-----
> From: Nathan Rixham [mailto:nrixham@gmail.com]
>
> you'll also find a performance upgrade if you load all sql
> results in to
> an array and close up the query / free the results before
> working on them.
>
> query
> for() {
> $results[] = $row;
> }
> close stuff
> work on results

Do you have any proof of this?
What would be the logic here?
Got some example benchmarks?
Got a URL of a whitepaper or something that discusses this?

I would think the only things that the mysql calls are doing is holding
pointers to the next record and DB handle. I can't imagine they are using
that much resources, nor would they be just spinning and waiting tying up
CPU cycles either.


I've never heard this to be the case.


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Close MySQL Result

Daevid Vincent wrote:
>> -----Original Message-----
>> From: Nathan Rixham [mailto:nrixham@gmail.com]
>>
>> you'll also find a performance upgrade if you load all sql
>> results in to
>> an array and close up the query / free the results before
>> working on them.
>>
>> query
>> for() {
>> $results[] = $row;
>> }
>> close stuff
>> work on results
>
> Do you have any proof of this?
> What would be the logic here?
> Got some example benchmarks?
> Got a URL of a whitepaper or something that discusses this?
>
> I would think the only things that the mysql calls are doing is holding
> pointers to the next record and DB handle. I can't imagine they are using
> that much resources, nor would they be just spinning and waiting tying up
> CPU cycles either.
>
>
> I've never heard this to be the case.
>

I have proof to the contrary :-)

echo number_format(memory_get_usage()) . " Bytes used before query\n";

$conn = mysql_connect('localhost', 'x', 'x');
$result = mysql_query("SELECT * FROM table_name");
$count = mysql_num_rows($result);

echo $count . " rows returned\n";

echo number_format(memory_get_usage()) . " Bytes used after query\n";

while($rows[] = mysql_fetch_assoc($result)) { }

echo number_format(memory_get_usage()) . " Bytes used after array
creation\n";

mysql_free_result($result);

echo number_format(memory_get_usage()) . " Bytes used after
mysql_free_result\n";
?>

65,588 Bytes used before query
6940 rows returned
67,264 Bytes used after query
6,376,612 Bytes used after array creation
6,376,756 Bytes used after mysql_free_result

--
Thanks!
-Shawn
http://www.spidean.com

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Close MySQL Result

Shawn McKenzie wrote:
> Daevid Vincent wrote:
>>> -----Original Message-----
>>> From: Nathan Rixham [mailto:nrixham@gmail.com]
>>>
>>> you'll also find a performance upgrade if you load all sql
>>> results in to
>>> an array and close up the query / free the results before
>>> working on them.
>>>
>>> query
>>> for() {
>>> $results[] = $row;
>>> }
>>> close stuff
>>> work on results
>> Do you have any proof of this?
>> What would be the logic here?
>> Got some example benchmarks?
>> Got a URL of a whitepaper or something that discusses this?
>>
>> I would think the only things that the mysql calls are doing is holding
>> pointers to the next record and DB handle. I can't imagine they are using
>> that much resources, nor would they be just spinning and waiting tying up
>> CPU cycles either.
>>
>>
>> I've never heard this to be the case.
>>
>
> I have proof to the contrary :-)
>
> > echo number_format(memory_get_usage()) . " Bytes used before query\n";
>
> $conn = mysql_connect('localhost', 'x', 'x');
> $result = mysql_query("SELECT * FROM table_name");
> $count = mysql_num_rows($result);
>
> echo $count . " rows returned\n";
>
> echo number_format(memory_get_usage()) . " Bytes used after query\n";
>
> while($rows[] = mysql_fetch_assoc($result)) { }
>
> echo number_format(memory_get_usage()) . " Bytes used after array
> creation\n";
>
> mysql_free_result($result);
>
> echo number_format(memory_get_usage()) . " Bytes used after
> mysql_free_result\n";
> ?>
>
> 65,588 Bytes used before query
> 6940 rows returned
> 67,264 Bytes used after query
> 6,376,612 Bytes used after array creation
> 6,376,756 Bytes used after mysql_free_result
>

yup appears i was talking bollocks :D lolol - confusion came on my part
from optimisation of a high traffic site over the past few years!

correction:

on simple scripts it is slower, marginally, (due to two for loops and
more data in memory)

however - in real world applications where such nasty practises as
escaping in and out of html to render results and secondary per row
queries or api calls are taken in to account this method of getting
everything out of the db in a single swift action gives the performance
boost - and more over on high traffic sites freeing up the connection /
query resources as quickly as possible makes a rather noticeable impact,
too many open connections and queries can be very nasty when your
getting a few hundred / thousand requests per second.

sorry 'bout that!

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Close MySQL Result

Interesting, I don't have any numerical benchmarks, but I saw a
performance benefit moving my result set into an array.

My case may have been extreme, I was creating a table 350 rows by 350
columns, several megabytes of output, and I found that building the
output directly from the query too slow.

When I moved my results into an array before I started, it moved to a
reasonable build time.

My theory was that I was not getting proper use out of my indexes in my
data set, or that the interface I was using to get to my MS-SQL tables
was inefficient in some other way.

I may not have "proof", but I will be inclined to use this for a
performance boost when working with large sets of data.



On 1/21/2010 6:45 PM, Nathan Rixham wrote:
> Shawn McKenzie wrote:
>
>> Daevid Vincent wrote:
>>
>>>> -----Original Message-----
>>>> From: Nathan Rixham [mailto:nrixham@gmail.com]
>>>>
>>>> you'll also find a performance upgrade if you load all sql
>>>> results in to
>>>> an array and close up the query / free the results before
>>>> working on them.
>>>>
>>>> query
>>>> for() {
>>>> $results[] = $row;
>>>> }
>>>> close stuff
>>>> work on results
>>>>
>>> Do you have any proof of this?
>>> What would be the logic here?
>>> Got some example benchmarks?
>>> Got a URL of a whitepaper or something that discusses this?
>>>
>>> I would think the only things that the mysql calls are doing is holding
>>> pointers to the next record and DB handle. I can't imagine they are using
>>> that much resources, nor would they be just spinning and waiting tying up
>>> CPU cycles either.
>>>
>>>
>>> I've never heard this to be the case.
>>>
>>>
>> I have proof to the contrary :-)
>>
>> >> echo number_format(memory_get_usage()) . " Bytes used before query\n";
>>
>> $conn = mysql_connect('localhost', 'x', 'x');
>> $result = mysql_query("SELECT * FROM table_name");
>> $count = mysql_num_rows($result);
>>
>> echo $count . " rows returned\n";
>>
>> echo number_format(memory_get_usage()) . " Bytes used after query\n";
>>
>> while($rows[] = mysql_fetch_assoc($result)) { }
>>
>> echo number_format(memory_get_usage()) . " Bytes used after array
>> creation\n";
>>
>> mysql_free_result($result);
>>
>> echo number_format(memory_get_usage()) . " Bytes used after
>> mysql_free_result\n";
>> ?>
>>
>> 65,588 Bytes used before query
>> 6940 rows returned
>> 67,264 Bytes used after query
>> 6,376,612 Bytes used after array creation
>> 6,376,756 Bytes used after mysql_free_result
>>
>>
> yup appears i was talking bollocks :D lolol - confusion came on my part
> from optimisation of a high traffic site over the past few years!
>
> correction:
>
> on simple scripts it is slower, marginally, (due to two for loops and
> more data in memory)
>
> however - in real world applications where such nasty practises as
> escaping in and out of html to render results and secondary per row
> queries or api calls are taken in to account this method of getting
> everything out of the db in a single swift action gives the performance
> boost - and more over on high traffic sites freeing up the connection /
> query resources as quickly as possible makes a rather noticeable impact,
> too many open connections and queries can be very nasty when your
> getting a few hundred / thousand requests per second.
>
> sorry 'bout that!
>
>


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Close MySQL Result

Always found the same myself on large datasets and when working with
high traffic sites; but cant replicate in a non-live environment or with
simple grinder style tests; so just follow the procedure as standard
practise in all my code now - likewise with dropping keep alive times on
apache servers and some other little tweaks.

regards!

Michael Schaefer wrote:
> Interesting, I don't have any numerical benchmarks, but I saw a
> performance benefit moving my result set into an array.
>
> My case may have been extreme, I was creating a table 350 rows by 350
> columns, several megabytes of output, and I found that building the
> output directly from the query too slow.
>
> When I moved my results into an array before I started, it moved to a
> reasonable build time.
>
> My theory was that I was not getting proper use out of my indexes in my
> data set, or that the interface I was using to get to my MS-SQL tables
> was inefficient in some other way.
>
> I may not have "proof", but I will be inclined to use this for a
> performance boost when working with large sets of data.
>
>
>
> On 1/21/2010 6:45 PM, Nathan Rixham wrote:
>> Shawn McKenzie wrote:
>>
>>> Daevid Vincent wrote:
>>>
>>>>> -----Original Message-----
>>>>> From: Nathan Rixham [mailto:nrixham@gmail.com]
>>>>>
>>>>> you'll also find a performance upgrade if you load all sql
>>>>> results in to
>>>>> an array and close up the query / free the results before
>>>>> working on them.
>>>>>
>>>>> query
>>>>> for() {
>>>>> $results[] = $row;
>>>>> }
>>>>> close stuff
>>>>> work on results
>>>>>
>>>> Do you have any proof of this?
>>>> What would be the logic here?
>>>> Got some example benchmarks?
>>>> Got a URL of a whitepaper or something that discusses this?
>>>>
>>>> I would think the only things that the mysql calls are doing is holding
>>>> pointers to the next record and DB handle. I can't imagine they are
>>>> using
>>>> that much resources, nor would they be just spinning and waiting
>>>> tying up
>>>> CPU cycles either.
>>>>
>>>>
>>>> I've never heard this to be the case.
>>>>
>>>>
>>> I have proof to the contrary :-)
>>>
>>> >>> echo number_format(memory_get_usage()) . " Bytes used before query\n";
>>>
>>> $conn = mysql_connect('localhost', 'x', 'x');
>>> $result = mysql_query("SELECT * FROM table_name");
>>> $count = mysql_num_rows($result);
>>>
>>> echo $count . " rows returned\n";
>>>
>>> echo number_format(memory_get_usage()) . " Bytes used after query\n";
>>>
>>> while($rows[] = mysql_fetch_assoc($result)) { }
>>>
>>> echo number_format(memory_get_usage()) . " Bytes used after array
>>> creation\n";
>>>
>>> mysql_free_result($result);
>>>
>>> echo number_format(memory_get_usage()) . " Bytes used after
>>> mysql_free_result\n";
>>> ?>
>>>
>>> 65,588 Bytes used before query
>>> 6940 rows returned
>>> 67,264 Bytes used after query
>>> 6,376,612 Bytes used after array creation
>>> 6,376,756 Bytes used after mysql_free_result
>>>
>>>
>> yup appears i was talking bollocks :D lolol - confusion came on my part
>> from optimisation of a high traffic site over the past few years!
>>
>> correction:
>>
>> on simple scripts it is slower, marginally, (due to two for loops and
>> more data in memory)
>>
>> however - in real world applications where such nasty practises as
>> escaping in and out of html to render results and secondary per row
>> queries or api calls are taken in to account this method of getting
>> everything out of the db in a single swift action gives the performance
>> boost - and more over on high traffic sites freeing up the connection /
>> query resources as quickly as possible makes a rather noticeable impact,
>> too many open connections and queries can be very nasty when your
>> getting a few hundred / thousand requests per second.
>>
>> sorry 'bout that!
>>
>>
>
>


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php