With the following code even though I fetch a single row - the whole dataset is downloaded (which takes few seconds):
$query = 'SELECT * FROM xxx WHERE id > :position ORDER BY id';
$stmt = $db->prepare($query);
$stmt->execute([
':position' => $position,
]);
while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
break;
}
I tried to find an option in PDO that would make it fetch rows on demand (or by reasonable batches), but failed to do so, at least I could not find it in the PDO documentation.
Postgresql for the given query is able to serve the first row in 0.28ms
as per the EXPLAIN ANALYZE
.
And my aim is to start processing rows as quicker as possible, even if further batches of rows will come with some network overhead.
How I determined that it fetches all the rows:
- Indirectly: it takes the same time to
break
after the first row or to iterate over the whole dataset - I captured the traffic with
tcpdump
and checked that it fetches all the rows.
So, the question is: is it possible to make PDO
to fetch in a row-by-row (or some reasonable small batches) on demand mode?