[TYPO3-dev] DB query iterator; worth a try?

[Ries van Twisk]

On Apr 23, 2010, at 12:08 AM, Jigal van Hemert wrote:

> Ries van Twisk wrote:
>> On Apr 22, 2010, at 4:23 PM, Jigal van Hemert wrote:
>>> I don't think the dataset is loaded in PHP allocated memory. It's  
>>> more
>>> likely that the database client receives the entire dataset or a  
>>> large
>>> part of it. That will be handled by the database client.
>>> Anyway, these are details that we don't have to worry about :-)
>> Actually, in this case you should. Because it can be a difference  
>> between a very slow
>> iteration (using a cursors fetching each record using DB  
>> communications) or a fast iterator.
>
> I think that I know what you mean! One would need to worry about the  
> method of retrieving data if the implementation of the iterator  
> would use a completely different method than we normally would use.
>
>> While looking at the source and it shows something like this:
>> static void php_mysql_do_query_general(zval **query, zval  
>> **mysql_link, int link_id, zval **db, int use_store, zval  
>> *return_value TSRMLS_DC)
>> ...
>> ...
>> ..
>>                    mysql_result = mysql_use_result(&mysql->conn);
>>                                while ((row =  
>> mysql_fetch_row(mysql_result))) {
>>                                        if (!strcmp("ALL", row[1])) {
>>                                                php_error_docref("http://www.mysql.com/doc 
>> " TSRMLS_CC, E_WARNING, "Your query requires a full tablescan  
>> (table %s, %s rows affected
>> ). Use EXPLAIN to optimize your query.", row[0], row[6]);
>
> This piece of code is part of the trace_mode only used for select  
> queries where it prefixes the query with "EXPLAIN ". That's why it  
> checks for "ALL" in row[1] (and a few lines later for "INDEX"). The  
> rows it gets are the output of the EXPLAIN query.
>

Yes you are right totally my bad and I was to fast .... :s

The loading doesn't happen in PHP ofcourse, but it happens in the  
mysql client library..

The whole point is that after the query is done, the resultset sits in  
some buffer ready to be read by whoever and
it consumes memory.
it doesn't come from the MySQL tables anymore because the locks needs  
to get released ASAP
for further data and you cannot have a client thumbing around fetching  
more data from the physical row.

The fetched data simply sits in a buffer read to be paste to a client.  
With cursors this works different!

Taking seeking for example

void STDCALL
mysql_data_seek(MYSQL_RES *result, my_ulonglong row)
{
   MYSQL_ROWS    *tmp=0;
   DBUG_PRINT("info",("mysql_data_seek(%ld)",(long) row));
   if (result->data)
     for (tmp=result->data->data; row-- && tmp ; tmp = tmp->next) ;
   result->current_row=0;
   result->data_cursor = tmp;
}

That just changes some pointers around, no mysql communication is  
happening!

But we both agree, a iteration should be about just as fast and just  
calling fetch rows.


Ries