php get size of pdo result (mysql)

Question

I would like to know how much data was retrieve from the database in a query. It does not need to be exact (+/- 20% is ok)

some ways that it could be done:

memory_get_usage() -> diff the value before and after the query. Could be problematic since memory can also change by other stuff changing in the meanwhile.
strlen(json_encode($result)) -> will double the memory usage.
recursively going through the result and calling str len on all attributes (could be slow)

Is there some more straightforward way to get the size of the result variable?

See https://stackoverflow.com/questions/52314941/cheapest-way-to-get-size-of-multidimensional-php-array — Barmar, Feb 01 '23 at 20:51
Why do you think option 1 would be problematic? As long as the statements are as close to the actual query as possible, then I'm not sure what else could affect it "in the meanwhile". `memory_get_usage`is unique to the current script, not system-wide — iainn, Feb 01 '23 at 20:53
Can't you just use `LENGTH` and concatenate all your fields? Like `SELECT (LENGTH ( CONCAT ( field, field, field ) ) )` This *should* return number in bytes? — Zak, Feb 01 '23 at 20:53
Why do you think option 3 could be slow? And why there is a recursion, if you always get strictly 2-dimensional array, so it's just 2 nested loops. Or, rather, one nested loop because you have one already , fetching data from database. — Your Common Sense, Feb 01 '23 at 20:59
Honestly there is no good way. You should tell us why you think that this information is important, and we can suggest a solution that is more appropriate. — Sammitch, Feb 02 '23 at 00:48

score -1 · Answer 1 · answered Feb 02 '23 at 01:06

Here's an example: I have a table of three rows, the total length should be 22.

mysql> select * from characters;
+----+-----------+--------+
| id | char_name | gender |
+----+-----------+--------+
|  1 | Harry     | m      |
|  2 | Ron       | m      |
|  3 | Hermione  | f      |
+----+-----------+--------+

Here's PHP code I tested (MySQL 8.0.32, PHP 8.2.1):

<?php

$db = new PDO('mysql:host=localhost;dbname=test', 'root', 'xxxx');

$result = $db->query("SELECT * FROM characters")->fetchAll(PDO::FETCH_ASSOC);

$ait = new RecursiveArrayIterator($result);
$rit = new RecursiveIteratorIterator($ait);

$sum = 0;
foreach ($rit as $key => $value) {
  echo "value: $value\n";
  if (isset($value)) {
    $sum += strlen($value);
  }
}

echo "sum: $sum\n";

Output:

value: 1
value: Harry
value: m
value: 2
value: Ron
value: m
value: 3
value: Hermione
value: f
sum: 22

php get size of pdo result (mysql)

1 Answers1