-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello,

the documentation says:
The returned result resource should be passed to mysql_fetch_array(), and other
functions for dealing with result tables, to access the returned data.

Does this mean that all the data is already fetched from the DB and loaded into
RAM and I only need the mysql_fetch_* functions to extract it ?

eg.

$query = mysql_query("SELECT * FROM `table`");

If in table are rows worth some megaBytes, are those megabytes already loaded
into RAM or is this only happening if if I use mysql_fetch_assoc/array etc.

eg.

$query = mysql_query("SELECT * FROM `table`");
// is the data already here loaded into RAM
while($result = mysql_fetch_assoc($query)) {
$newArray[] = $result;
// or at this point ?
}


regards,
johannes keßler
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.14 (GNU/Linux)

iEYEARECAAYFAktwKHUACgkQE++2Zdc7Etd1PwCfcJttayTQuI mXhipHk5ocdY2M
VdMAn2nZJIyCaThw+zXDRYBaDqx7wJCR
=b1CY
-----END PGP SIGNATURE-----

Johannes Keßler wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hello,
>
> the documentation says:
> The returned result resource should be passed to mysql_fetch_array(), and other
> functions for dealing with result tables, to access the returned data.
>
> Does this mean that all the data is already fetched from the DB and loaded into
> RAM and I only need the mysql_fetch_* functions to extract it ?
>
> eg.
>
> $query = mysql_query("SELECT * FROM `table`");
>
> If in table are rows worth some megaBytes, are those megabytes already loaded
> into RAM or is this only happening if if I use mysql_fetch_assoc/array etc.
>
> eg.
>
> $query = mysql_query("SELECT * FROM `table`");
> // is the data already here loaded into RAM
> while($result = mysql_fetch_assoc($query)) {
> $newArray[] = $result;
> // or at this point ?
> }
>
>
> regards,
> johannes keßler
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v2.0.14 (GNU/Linux)
>
> iEYEARECAAYFAktwKHUACgkQE++2Zdc7Etd1PwCfcJttayTQuI mXhipHk5ocdY2M
> VdMAn2nZJIyCaThw+zXDRYBaDqx7wJCR
> =b1CY
> -----END PGP SIGNATURE-----

These are mysql calls. Try asking in comp.databases.mysql.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Mon, 08 Feb 2010 16:06:30 +0100, Johannes Keßler wrote:
>
> Hello,
>
> the documentation says:
> The returned result resource should be passed to mysql_fetch_array(), and other
> functions for dealing with result tables, to access the returned data.
>
> Does this mean that all the data is already fetched from the DB and loaded into
> RAM and I only need the mysql_fetch_* functions to extract it ?
>
> eg.
>
> $query = mysql_query("SELECT * FROM `table`");
>
> If in table are rows worth some megaBytes, are those megabytes already loaded
> into RAM or is this only happening if if I use mysql_fetch_assoc/array etc.
>
> eg.
>
> $query = mysql_query("SELECT * FROM `table`");
> // is the data already here loaded into RAM
> while($result = mysql_fetch_assoc($query)) {
> $newArray[] = $result;
> // or at this point ?
> }

hmmm... If I remember correctly, it doesn't fetch ALL the data
necessarily, just enough to fill the connection buffers. Looping through
and filling an array will (eventually) put all the data into memory (the
array) but that runs the risk of becoming Very Large, possibly too large
for the allowed memory of the php process.

--
93. If I decide to hold a double execution of the hero and an underling
who failed or betrayed me, I will see to it that the hero is
scheduled to go first.
--Peter Anspach's list of things to do as an Evil Overlord

On Feb 8, 10:06*am, Johannes Keßler <m...@bananas-playground.net>
wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hello,
>
> the documentation says:
> The returned result resource should be passed to mysql_fetch_array(), andother
> functions for dealing with result tables, to access the returned data.
>
> Does this mean that all the data is already fetched from the DB and loaded into
> RAM and I only need the mysql_fetch_* functions to extract it ?
>
> eg.
>
> $query = mysql_query("SELECT * FROM `table`");
>
> If in table are rows worth some megaBytes, are those megabytes already loaded
> into RAM or is this only happening if if I use mysql_fetch_assoc/array etc.
>
> eg.
>
> $query = mysql_query("SELECT * FROM `table`");
> // is the data already here loaded into RAM
> while($result = mysql_fetch_assoc($query)) {
> * *$newArray[] = $result;
> * *// or at this point ?
>
> }
>
> regards,
> johannes keßler

You could always run some tests with memory_get_usage()

On Feb 8, 7:45*pm, matt <matthew.leonha...@gmail.com> wrote:
> On Feb 8, 10:06*am, Johannes Keßler <m...@bananas-playground.net>
> wrote:
>
>
>
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
>
> > Hello,
>
> > the documentation says:
> > The returned result resource should be passed to mysql_fetch_array(), and other
> > functions for dealing with result tables, to access the returned data.
>
> > Does this mean that all the data is already fetched from the DB and loaded into
> > RAM and I only need the mysql_fetch_* functions to extract it ?
>
> > eg.
>
> > $query = mysql_query("SELECT * FROM `table`");
>
> > If in table are rows worth some megaBytes, are those megabytes already loaded
> > into RAM or is this only happening if if I use mysql_fetch_assoc/array etc.
>
> > eg.
>
> > $query = mysql_query("SELECT * FROM `table`");
> > // is the data already here loaded into RAM
> > while($result = mysql_fetch_assoc($query)) {
> > * *$newArray[] = $result;
> > * *// or at this point ?
>
> > }
>
> > regards,
> > johannes keßler
>
> You could always run some tests with memory_get_usage()

No - the client libs will buffer the results (potentially the complete
result set even before any call to mysql_fetch_....) but outside the
PHP memory area - IIRC memory_get_usage only reports memory allocated
by PHP.

C.

C. (http://symcbean.blogspot.com/) schreef:
> On Feb 8, 7:45 pm, matt <matthew.leonha...@gmail.com> wrote:
>> On Feb 8, 10:06 am, Johannes Keßler <m...@bananas-playground.net>
>> wrote:
>>
>>
>>
>>> -----BEGIN PGP SIGNED MESSAGE-----
>>> Hash: SHA1
>>> Hello,
>>> the documentation says:
>>> The returned result resource should be passed to mysql_fetch_array(), and other
>>> functions for dealing with result tables, to access the returned data.
>>> Does this mean that all the data is already fetched from the DB and loaded into
>>> RAM and I only need the mysql_fetch_* functions to extract it ?
>>> eg.
>>> $query = mysql_query("SELECT * FROM `table`");
>>> If in table are rows worth some megaBytes, are those megabytes already loaded
>>> into RAM or is this only happening if if I use mysql_fetch_assoc/array etc.
>>> eg.
>>> $query = mysql_query("SELECT * FROM `table`");
>>> // is the data already here loaded into RAM
>>> while($result = mysql_fetch_assoc($query)) {
>>> $newArray[] = $result;
>>> // or at this point ?
>>> }
>>> regards,
>>> johannes keßler
>> You could always run some tests with memory_get_usage()
>
> No - the client libs will buffer the results (potentially the complete
> result set even before any call to mysql_fetch_....) but outside the
> PHP memory area - IIRC memory_get_usage only reports memory allocated
> by PHP.
>
> C.


Hi C,

Still, using memory_get_usage() could help determine if the data is send
to PHP or is at mySQL's buffers untill fetched.

A query with huge resultset should increase memory_get_usage() is PHP is
holding the resultset. If MySQL holds it it should not give an increase.
I think....

But then again: things might be more complicated then I present them here.

Regards,
Erwin Moller

--
"There are two ways of constructing a software design: One way is to
make it so simple that there are obviously no deficiencies, and the
other way is to make it so complicated that there are no obvious
deficiencies. The first method is far more difficult."
-- C.A.R. Hoare

On Feb 9, 7:55*am, "C. (http://symcbean.blogspot.com/)"
<colin.mckin...@gmail.com> wrote:
> On Feb 8, 7:45*pm, matt <matthew.leonha...@gmail.com> wrote:
>
>
>
> > On Feb 8, 10:06*am, Johannes Keßler <m...@bananas-playground.net>
> > wrote:
>
> > > -----BEGIN PGP SIGNED MESSAGE-----
> > > Hash: SHA1
>
> > > Hello,
>
> > > the documentation says:
> > > The returned result resource should be passed to mysql_fetch_array(),and other
> > > functions for dealing with result tables, to access the returned data..
>
> > > Does this mean that all the data is already fetched from the DB and loaded into
> > > RAM and I only need the mysql_fetch_* functions to extract it ?
>
> > > eg.
>
> > > $query = mysql_query("SELECT * FROM `table`");
>
> > > If in table are rows worth some megaBytes, are those megabytes already loaded
> > > into RAM or is this only happening if if I use mysql_fetch_assoc/array etc.
>
> > > eg.
>
> > > $query = mysql_query("SELECT * FROM `table`");
> > > // is the data already here loaded into RAM
> > > while($result = mysql_fetch_assoc($query)) {
> > > * *$newArray[] = $result;
> > > * *// or at this point ?
>
> > > }
>
> > > regards,
> > > johannes keßler
>
> > You could always run some tests with memory_get_usage()
>
> No - the client libs will buffer the results (potentially the complete
> result set even before any call to mysql_fetch_....) but outside the
> PHP memory area - IIRC memory_get_usage only reports memory allocated
> by PHP.

I guess I assumed "loaded into RAM" in the OP to mean "loaded into
PHP's memory space." Either way there should still be a way to
interrupt the process to analyze system memory usage:

$sth = mysql_query($queryWithLargeDataset);

echo "Memory usage (mysql_query): " . memory_get_usage();
fgets(STDIN);

while ($result[] = mysql_fetch_assoc($sth));

echo "Memory usage (mysql_fetch): " . memory_get_usage();
fgets(STDIN);

Using other system tools to determine if the memory is getting loaded
outside of PHP after the mysql_query() call. If the result set is
significant enough (and the system is quiet enough), it should be
pretty easy to tell looking at top, etc.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 09/02/10 14:30, matt wrote:
> On Feb 9, 7:55 am, "C. (http://symcbean.blogspot.com/)"
> <colin.mckin...@gmail.com> wrote:
>> On Feb 8, 7:45 pm, matt <matthew.leonha...@gmail.com> wrote:
>>
>>
>>
>>> On Feb 8, 10:06 am, Johannes Keßler <m...@bananas-playground.net>
>>> wrote:
>>
>>>> -----BEGIN PGP SIGNED MESSAGE-----
>>>> Hash: SHA1
>>
>>>> Hello,
>>
>>>> the documentation says:
>>>> The returned result resource should be passed to mysql_fetch_array(), and other
>>>> functions for dealing with result tables, to access the returned data.
>>
>>>> Does this mean that all the data is already fetched from the DB and loaded into
>>>> RAM and I only need the mysql_fetch_* functions to extract it ?
>>
>>>> eg.
>>
>>>> $query = mysql_query("SELECT * FROM `table`");
>>
>>>> If in table are rows worth some megaBytes, are those megabytes already loaded
>>>> into RAM or is this only happening if if I use mysql_fetch_assoc/array etc.
>>
>>>> eg.
>>
>>>> $query = mysql_query("SELECT * FROM `table`");
>>>> // is the data already here loaded into RAM
>>>> while($result = mysql_fetch_assoc($query)) {
>>>> $newArray[] = $result;
>>>> // or at this point ?
>>
>>>> }
>>
>>>> regards,
>>>> johannes keßler
>>
>>> You could always run some tests with memory_get_usage()
>>
>> No - the client libs will buffer the results (potentially the complete
>> result set even before any call to mysql_fetch_....) but outside the
>> PHP memory area - IIRC memory_get_usage only reports memory allocated
>> by PHP.
>
> I guess I assumed "loaded into RAM" in the OP to mean "loaded into
> PHP's memory space." Either way there should still be a way to
> interrupt the process to analyze system memory usage:
>
> $sth = mysql_query($queryWithLargeDataset);
>
> echo "Memory usage (mysql_query): " . memory_get_usage();
> fgets(STDIN);
>
> while ($result[] = mysql_fetch_assoc($sth));
>
> echo "Memory usage (mysql_fetch): " . memory_get_usage();
> fgets(STDIN);
>
> Using other system tools to determine if the memory is getting loaded
> outside of PHP after the mysql_query() call. If the result set is
> significant enough (and the system is quiet enough), it should be
> pretty easy to tell looking at top, etc.
>

Thank you for all the answers.

I ran a simple test.

the DB Table was 3MB (reported by phpMyAdmin tool)
This was the script:

<?php
$db_con = mysql_connect("localhost","user","test");
$db_sel = mysql_select_db("cundion_test",$db_con);

echo "Memory usage (start): " . memory_get_usage()."\n";

$query = mysql_query("SELECT * FROM nortys_user_process_worktime_reg");

echo "Memory usage (mysql_query): " . memory_get_usage()."\n";
while($result = mysql_fetch_assoc($query)) {
$new[] = $result;
}

echo "Memory usage (mysql_fetch): " . memory_get_usage()."\n";

mysql_free_result($query);

echo "Memory usage (mysql_free): " . memory_get_usage()."\n";
?>

this was the result:

Memory usage (start): 71804
Memory usage (mysql_query): 72044
Memory usage (mysql_fetch): 20929384
Memory usage (mysql_free): 20929288

this looks like the fetch loaded the data into php RAM space and not the query
call itself.

This check could be used to avoid memory warnings/errors in php if the fetched
data is too much. (?)

regards,
johannes keßler
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.14 (GNU/Linux)

iEYEARECAAYFAktxalMACgkQE++2Zdc7EtfeDwCgjM5NqkjPyt pPD/z115LIFsff
IisAn2TWCcxA2ZkhhHnyECswmVSMZWtw
=32iZ
-----END PGP SIGNATURE-----

matt wrote:
> On Feb 9, 7:55 am, "C. (http://symcbean.blogspot.com/)"
> <colin.mckin...@gmail.com> wrote:
>> On Feb 8, 7:45 pm, matt <matthew.leonha...@gmail.com> wrote:
>>
>>
>>
>>> On Feb 8, 10:06 am, Johannes Keßler <m...@bananas-playground.net>
>>> wrote:
>>>> -----BEGIN PGP SIGNED MESSAGE-----
>>>> Hash: SHA1
>>>> Hello,
>>>> the documentation says:
>>>> The returned result resource should be passed to mysql_fetch_array(), and other
>>>> functions for dealing with result tables, to access the returned data.
>>>> Does this mean that all the data is already fetched from the DB and loaded into
>>>> RAM and I only need the mysql_fetch_* functions to extract it ?
>>>> eg.
>>>> $query = mysql_query("SELECT * FROM `table`");
>>>> If in table are rows worth some megaBytes, are those megabytes already loaded
>>>> into RAM or is this only happening if if I use mysql_fetch_assoc/array etc.
>>>> eg.
>>>> $query = mysql_query("SELECT * FROM `table`");
>>>> // is the data already here loaded into RAM
>>>> while($result = mysql_fetch_assoc($query)) {
>>>> $newArray[] = $result;
>>>> // or at this point ?
>>>> }
>>>> regards,
>>>> johannes keßler
>>> You could always run some tests with memory_get_usage()
>> No - the client libs will buffer the results (potentially the complete
>> result set even before any call to mysql_fetch_....) but outside the
>> PHP memory area - IIRC memory_get_usage only reports memory allocated
>> by PHP.
>
> I guess I assumed "loaded into RAM" in the OP to mean "loaded into
> PHP's memory space." Either way there should still be a way to
> interrupt the process to analyze system memory usage:
>
> $sth = mysql_query($queryWithLargeDataset);
>
> echo "Memory usage (mysql_query): " . memory_get_usage();
> fgets(STDIN);
>
> while ($result[] = mysql_fetch_assoc($sth));
>
> echo "Memory usage (mysql_fetch): " . memory_get_usage();
> fgets(STDIN);
>
> Using other system tools to determine if the memory is getting loaded
> outside of PHP after the mysql_query() call. If the result set is
> significant enough (and the system is quiet enough), it should be
> pretty easy to tell looking at top, etc.
>

how do you define 'outside of php' since its *client* libraries are
essentially 'owned' buy the php process anyway..

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 09/02/10 16:30, The Natural Philosopher wrote:
> matt wrote:
>> On Feb 9, 7:55 am, "C. (http://symcbean.blogspot.com/)"
>> <colin.mckin...@gmail.com> wrote:
>>> On Feb 8, 7:45 pm, matt <matthew.leonha...@gmail.com> wrote:
>>>
>>>
>>>
>>>> On Feb 8, 10:06 am, Johannes Keßler <m...@bananas-playground.net>
>>>> wrote:
>>>>> -----BEGIN PGP SIGNED MESSAGE-----
>>>>> Hash: SHA1
>>>>> Hello,
>>>>> the documentation says:
>>>>> The returned result resource should be passed to
>>>>> mysql_fetch_array(), and other
>>>>> functions for dealing with result tables, to access the returned data.
>>>>> Does this mean that all the data is already fetched from the DB and
>>>>> loaded into
>>>>> RAM and I only need the mysql_fetch_* functions to extract it ?
>>>>> eg.
>>>>> $query = mysql_query("SELECT * FROM `table`");
>>>>> If in table are rows worth some megaBytes, are those megabytes
>>>>> already loaded
>>>>> into RAM or is this only happening if if I use
>>>>> mysql_fetch_assoc/array etc.
>>>>> eg.
>>>>> $query = mysql_query("SELECT * FROM `table`");
>>>>> // is the data already here loaded into RAM
>>>>> while($result = mysql_fetch_assoc($query)) {
>>>>> $newArray[] = $result;
>>>>> // or at this point ?
>>>>> }
>>>>> regards,
>>>>> johannes keßler
>>>> You could always run some tests with memory_get_usage()
>>> No - the client libs will buffer the results (potentially the complete
>>> result set even before any call to mysql_fetch_....) but outside the
>>> PHP memory area - IIRC memory_get_usage only reports memory allocated
>>> by PHP.
>>
>> I guess I assumed "loaded into RAM" in the OP to mean "loaded into
>> PHP's memory space." Either way there should still be a way to
>> interrupt the process to analyze system memory usage:
>>
>> $sth = mysql_query($queryWithLargeDataset);
>>
>> echo "Memory usage (mysql_query): " . memory_get_usage();
>> fgets(STDIN);
>>
>> while ($result[] = mysql_fetch_assoc($sth));
>>
>> echo "Memory usage (mysql_fetch): " . memory_get_usage();
>> fgets(STDIN);
>>
>> Using other system tools to determine if the memory is getting loaded
>> outside of PHP after the mysql_query() call. If the result set is
>> significant enough (and the system is quiet enough), it should be
>> pretty easy to tell looking at top, etc.
>>
>
> how do you define 'outside of php' since its *client* libraries are
> essentially 'owned' buy the php process anyway..
>

I was refering to matt. He came up with this idea.

The only thing we can says for sure the mysql_fetch functions are loading the
data into the PHP RAM space and not the query call.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.14 (GNU/Linux)

iEYEARECAAYFAktxgOQACgkQE++2Zdc7EteIbACffAFSlh7twE aKloZpiWhLo30l
6iUAnjblWHGiDo16pW5atKhtZVUHDfFw
=1J8T
-----END PGP SIGNATURE-----