Why is PHP using a lot of memory to store a query result

user_4378701 0
Viewed 382

I use Laravel 8 to perform a query on a MySQL 8 table using the query builder directly to avoid Eloquent overhead but I'm getting a lot of memory consumption anyway.
To show you an example, I perform the following query to select exactly 300 000 elements.

My code looks like this:

$before = memory_get_usage();
$q_coords = DB::table('coords')->selectRaw('alt, lat, lng, id')
    ->where('active', 1)->take(300000)->get();

$after = memory_get_usage();
echo ($after - $before);

It displays 169760384 which means something like 169MB if I'm not mistaking..
Looks like a lot to me because in my query I only asked for 2 float and 2 bigInt, which represents something like 4 x 8 bytes (32 bytes).
And.. 32 x 300 000 records ~= 9600000 (almost 10MB).

How is that even possible that it uses so much memory? I am very surprised.

EDIT

I also tried using PDO directly, same result.

$query = DB::connection()->getPdo()->query("select alt, lat, lng, id from coords WHERE active = 1 LIMIT 300000");
$q_coords = $query->fetchAll();
laravel-query-builder laravel mysql php
3 Answers

Because thet are represented as PHP objects in memory and not just as their raw data usage. However there is a solution to limit the memory usage: chunk

https://blackdeerdev.com/laravel-chunk-vs-cursor/

Chunk: It will “paginate” your query, this way you use less memory.

user_27545990

Since Laravel Query Builder uses stdObj to represent it results, you will have a lot of overhead:

Each object will store the value of the row itself, and the names of each column. So your 32 bytes turns into a lot of bytes.

user_34293230

In PHP each variable is handled with a specific data structure to allow dynamic typing, garbage collection and more..
You can see here a (pretty old but still ok) article: link

You can also see that arrays have a more specific processing, because it need a bucket, for example to store array keys which are considered as Strings.

All of that means there is (according to the article) approximately 144 bytes of data used to store an element of an array.
Well, while I can't explain EXACTLY your result, I can still tell you that in your case have something like this:

300 000 * 144 * 4 = 172 800 000

Which means 300000 rows of 4 variables with 144 bytes by variable.

As you can see it's not that far away from what you got even if my maths are not taking into account the improvement done in PHP 7 and other factors...

user_129308780
Related