Incremental rows retrieval

[Justas Butkus]

Hi all.

Is there a chance, without bypassing `wpdb`, to get resulting records
from `SELECT` query in an incremental manner, instead of current
behaviour when `wpdb` pre-fetches them all?

Let's assume I have a query which might result in tens of thousands of rows.
Holding it all in memory (like wpdb currently does when I execute
`SELECT`) would require a lot of memory.
If I were able to process them incrementally memory consumption may be
low, as they are fed to external resource and then discarded.

I have considered following alternative: using multiple queries and
adding `WHERE primary_key > $last_value ORDER BY primary_key ASC` to
subsequent ones.
It seems to be the best solution given MySQL index optimisation.
The problem here is consistency - if rows has cross dependency there is
no guarantee that all relates rows will fall into a single interval.

Though - is there a chance to get records in an incremental manner
without breaking wpdb interfaces?

[Justas Butkus]

Hello.

It is just an extension over the mentioned `WHERE primary_key >
$last_value ORDER BY primary_key ASC`. In that case LIMIT will likely be
used, just with one index, for example `LIMIT 100`, instead of `LIMIT
$last_count, 100`. The difference is that LIMIT on it's own is not as
efficient as approach with LIMIT solely (see [1] and [2] bellow for some
authoritative details). And that doesn't void inconsistency probability,
as I mentioned.

Thanks for taking time.
--
Regards,
Justas Butkus

[1]:
http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/
[2]:
http://www.percona.com/pdf-canonical-header?path=files/presentations/ppc2009/PPC2009_mysql_pagination.pdf

Is there any reason you can’t use MySQL’s limit described here http://dev.mysql.com/doc/refman/5.0/en/select.html ?