MySQL and PHP... result sets

[shadedecho]

The following questions are for the advanced readers out there, who really understand at the core level how PHP and MySQL interoperate through the MySQL API in PHP, and how they manage communication and memory resources:

When I issue a mysql_query() command, and let's say it has 10,000,000 rows to return... does MySQL create the whole result set, all at once, and send it back to PHP in one big chunk, and the PHP deals with that by sticking the whole chunk into memory and returning back to my application a pointer to it?

Or is there some sort of paging/cache'ing going on where the database only returns parts of the result set at a time, as needed/requested, so that PHP doesn't have to stick it all into memory? (and if so, how do you tune, adjust this stuff for performance)?

Is there a way for performance sake to go through like maybe some arbitrary chunk size at a time, like 100 rows, and have the database just keep streaming that data back to PHP as it's requested?

Is this how mysql_unbuffered_query() works? I'm curious how you write a loop to process row-by-row when PHP doesn't have all the rows at the start of your loop... does each successive call to mysql_fetch_row() sit and wait for either the next row or a false from the DB to mean the result set is done? or does it immediately return false if the database is still executing and has no more rows to return yet?

Also, is the way that PHP stores the result set in memory as a "resource" as they call it, MORE efficient than munging through the whole result set, row by row, sticking it in an 2d array, and then calling mysql_free_result() to free the resource memory? Or are they stored exactly the same in memory (ie, is the resource result set compressed or anything like that, like over the communicate channel from the DB)?

And can you modify the information IN the resource result set, or do you have to grab it all out of the resource into a PHP array before modifying it?

 

[Olavxxx]

Please read this:

http://no2.php.net/manual/en/function.mysql-unbuffered-query.php

Also read the user comments below..

Hope this helps you a bit, if not much.

 

[shadedecho]

I had already seen and read this page, as well as several others in the MySQL and PHP manuals.

My curiosity stems FROM those pages, in fact, because it seems that what is presented there (and rightly so) is the API perspective (ie, how to USE the functions) not how they actually work "under the hood".

I was hoping to see if anyone out there actually had any low-level experience developing or tinkering with the inner workings of the PHP and MySQL API's, and how they actually perform their work. For instance, my question about HOW PHP stores the returned result set into a "resource", and whether that is more efficient than the equivalent of reading the rows into a 2d array and then releasing the resource memory.

My experience has been that the way an API document tells you a function works, and how it REALLY works under the hood are sometimes different, and they CAN sometimes produce different benchmarking results than expected.

I have been using all these functions for many years, on many apps... I am seeking to gain a better knowledge of the "insides" of these tools so I can be better and more efficient at what I do, based on the specific requirements of my application tasks.

 

[Olavxxx]

mysql_unbuffered_query() sends a SQL query query to MySQL, without fetching and buffering the result rows automatically, as mysql_query() does. On the one hand, this saves a considerable amount of memory with SQL queries that produce large result sets. On the other hand, you can start working on the result set immediately after the first row has been retrieved: you don't have to wait until the complete SQL query has been performed.

you must retrieve all the rows even if you determine in mid-retrieval that you've found the information you were looking for.

I'm sorry that I cannot offer you more than those quotes, but I think it means:

sql drops the results to the server, not to php.
php retrieves it row by row, while looping the recordset..

eg. it will be faster in my mind..

you cannot "abort" the loop. You have to finish it (loop all records)

with it, you can gain access to the first loop, before downloading the rest.

I guess for 10.000 rows, you might save some time, as then you can order by something..

 

[ericbrunson]

Use the Source, Luke. ;-)

I can tell you how PHP interoperates with Oracle, but that's because I read the source code for the Oracle interface in PHP and I happened to know how Oracle handles its queries from my Oracle DBA classes.

I'm sure MySQL handles the buffering differently, and I'd have to download the source for the PHP/MySQL interface so see if it buffers the same way as with Oracle.

But, let's perform an Einsteinian "thought experiment":

Do you think MySQL developers have spent a lot of time making sure they allocate and release memory efficently?

Do you think that PHP developers could do significanty better than the MySQL developers at allocating and deallocating memory?

Even if PHP *is* significantly more efficient at managing memory than MySQL, do you think it is efficient to copy the contents of a PHP memory structure into a *different* PHP memory structure to save resources?

Since PHP only buffers a cursor of the query when querying an Oracle DB (and it's not a great leap to assume they are doing so with MySQL, too) and Oracle doesn't even read the records it needs from disk until it's ready to send them to the requestor, then releases the memory, I think it's safe to say the answer is: No, you probably won't gain anything by reading the data into your own array.

That's the DB equivalent of my biggest pet-Perl-peve when the first thing the program does is "slurp" an entire file into memory so they can iterate over the lines. I've sent many a would-be scripter in search of the mmap() man page when I see them doing that. Then require them to explain why it's such a bad practice before I tell them what I changed their password to and explain to them why I killed their script after it started sucking 99% of the free memory on the server and paging was accounting for %90 of the CPU usage. But I digress, let me step off my soapbox.

It's been my experience that the best way to manage your memory in a high level language like Perl, PHP or Python is to optimize your queries (do you really need 10K records? are you sure? are you selecting more fields than you need? "select *" is evil), use them as quickly as possible and dump the file handles (releasing locks and the memory) as soon as you're done.

I know this isn't the answer you were explicitly asking for, but if the question still nags you from an academic or philosophical standpoint, you can download the source as well as I can.