What is a result resource id actually pointing to?

[Joe_Rocket]

Hi,

Sorry if this question is elementary...

I'm just wondering about efficiency in database queries. At work, I need to access data from MySQL, MS SQL, and Oracle. I was wondering, when you query from PHP and you have the result resource id, what memory is that result data in? The database? or PHP?

I've been through the mechanics of connecting and grabbing data from all, but never read an explanation of what is happening and where it's happening behind the scenes.

So, you've done the query, you have a result pointer. Before you fetch a row, all rows, or anything, where is the data, and should I vary how I grab the data depending on whether it's a local DB, or a remote DB for efficiency sake?

Thanks

[markl999]

A result resource is just a variable holding a reference to an external resource. So the actual result set is stored in the database and PHP holds a reference to this. The only real consideration is wether to free that result set with mysql_free_result() for example, but that's only needed in rare circumstances as the result is freed automatically when the script ends.

[clamcrusher]

are you sure markl999?

the existance of this function makes me think mysql_query() buffers the results into php memory:
mysql_unbuffered_query()

or does that just tell mysql to not buffer it?

[agoossens]

are you sure markl999?

the existance of this function makes me think mysql_query() buffers the results into php memory:
mysql_unbuffered_query()

or does that just tell mysql to not buffer it?

mysql_query() fetches and buffers the entire result set. mysql_unbuffered_query() does not - it will return rows as soon as they are available (even before the query has finished executing!). Advantages: less memory, faster access to data. Disadvantages: no mysql_num_rows() or mysql_data_seek(), and you must finish reading the result set before you can send a new query.

So, you've done the query, you have a result pointer. Before you fetch a row, all rows, or anything, where is the data, and should I vary how I grab the data depending on whether it's a local DB, or a remote DB for efficiency sake?

The data usually sits in PHP memory. Some functions, like mysql_unbuffered_query, conserve memory by only fetching rows as they are read, but they have a cost.

Really, unless you're working with massive result sets and getting large numbers of simultaneous hits, it's not worth the hassle. If it does become a hassle, try fetching smaller result sets (LIMIT statement).

Don't become too overly concerned with premature optimization. Hardware is cheap. Programmer time is not.

[markl999]

are you sure markl999?

I thought I was .. but I was wrong Thanks for the correction.