A query with 2 leftjoins in a database with more than 2 thousand records eats up all the memory and processor and hangs, what's wrong?

Question

SQL

A query with 2 leftjoins to a database with more than 2 thousand records eats up all the memory / processor and hangs in Mysqld processes, what's wrong?

PHP Fatal error:  Allowed memory size of xxxxxxxxx bytes exhausted (tried to allocate xxxxx bytes) in vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 336

I have a request like this:

$data = Model::where('table1.name', $name)
        ->leftJoin('table2', 'table1.phone', '=', 'table2.phone')
                ->leftjoin('table3', 'table1.phone', '=', 'table3.phone')
                ->select('table3.email', 'table3.param1', 'table3.param2', 'table3.param3', 'table2.city', 'table2.info', 'table2.info2', 'table2.info3', 'table1.name', 'table1.phone', 'table1.data', 'table1.data1', 'table1.data2', 'table1.data3', 'table1.data4 AS new', 'table1.created_at', 'table1.id')
                ->get();

return Datatables::of($data)
            ->addColumn('edit', '')
            ->editColumn('data', function (Model $model) {
                $md = ($model->data == 0 ? 'yes' : $model->data);
                return $md;
            })
            ->editColumn('new', function (Model $model) {
                $new = ($model->new ? 'yes' : 'no');
                return $new;
            })
            ->editColumn('data1', function (Model $model) {
                $dt = ($model->data1 == 1 ? 'yes' : 'no');
                return $dt;
            })
            ->editColumn('created_at', function (Model $model) {
                $date = date('Y-m-d H:i:s', strtotime($model->created_at) ); // H:i:s
                return $date;
            })
            ->rawColumns(['edit'])
            ->make(true);

What am I doing wrong? After all, this is not a large number of records.

Answer 1

I abandoned datatables in favor of a paginator, everything became very fast and convenient.

$data = $this->postback->whereNotNull('table1.phone')
        ->where('table1.name', $name)
        ->leftJoin('table2', 'table1.phone', '=', 'table2.phone')
        ->leftjoin('table3', 'table1.phone', '=', 'table3.phone')
        ->select(
          'table3.email', 'table3.param1', 'table3.param2', 
          'table2.info', 'table2.info1', 'table2.info2', 'table2.info3', 'table2.info4', 
          'table1.name', 'table1.data', 'table1.data1', 'table1.data2', 'table1.data3', 'table1.phone', 'table1.data4 AS new', 'table1.created_at', 'table1.id'
        )
        ->orderBy('table1.created_at','asc')
        ->paginate($paginator);
    return $data;

PS By using relations instead of leftjoin, the table became even faster.

Answer 2

It's not about asking at all. Memory eats not base and PHP. First you get these 2k records, put them in memory, then you create a second object and also put it in memory + generate JSON from these records, and this is a very expensive resource operation

Answer 3

Are there the same values ​​in the tables, and in large quantities? Like an empty phone? If, say, there are 100 empty phones in each table, then the result will be at least 1,000,000 rows