Запрос с 2-мя leftjoin в базу с более 2-х тысяч записей, сжирает всю память и процессор и виснет, что не так?

Question

[TanykaGURU]

Запрос с 2-мя leftjoin в базу с более 2-х тысяч записей, сжирает всю память/процессор и виснет в процессах Mysqld, что не так?

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

Имею такой запрос:

$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);

Что я делаю не так? Ведь это еще не большое количество записей.

Comments

[Евгений]

2к записей это смешно, не должно быть такого

[Антон Антон]

Сделай Model::where('table1.name', $name)...->dd(); и покажи результат

[Евгений]

Во всех таблицах на полях phone стоят индексы?

[Константин Б.]

Евгений, это относится к скорости запроса, но никак не к памяти которая закончилась

[TanykaGURU]

Антон Антон,

"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` from `table1` left join `table2` on `table1`.`phone` = `table2`.`phone` left join `table3` on `table1`.`phone` = `table3`.`phone` where `table1`.`name` = ? ◀"

array:1 [▼
  0 => "name"
]

Евгений, Да

[Евгений]

Константин Б., чет затупил, действительно...

[Евгений]

Константин Б., только познаю ларку, и препод говорил, что сложные запросы бывает профитнее писать через execute(), это не тот случай?)

[Константин Б.]

Евгений, тут дело не в сложности запроса, а количестве получаемых и обрабатываемых данных после. Чаще всего такое лечится постраничным разбиением данных, чего по религиозным соображениям автор избегает

Answer 1

[TanykaGURU]

Отказался от datatables, в пользу пагинатора, все стало очень шустрым и удобным.

$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;

P.S. использовав отношения вместо leftjoin, таблица еще быстрее стала.

Answer 2

[Константин Б.]

Дело тут не в запросе вовсе. Память жрет не база а PHP. Сначала ты получаешь эти 2к записей, кладешь их в память, далее ты создаешь второй объект и также кладешь его в память + генерируешь JSON из этих записей, а это очень дорогая операция по ресурсам

Comments

[TanykaGURU]

Так и что делать в этом случае, лучше всего?

[Константин Б.]

TanykaGURU, ну если ты уверен что твой запрос на 100% верен и оптимален, то нужно оптимизировать вывод большого количества данных например пагинацией

[TanykaGURU]

Константин Б., про пагинацию тоже была мысль. И с пагинацией не было проблем никогда при больших количествах данных. Конечно хочется чтобы все данные разом грузились.

[Константин Б.]

TanykaGURU, ваш запрос возвращает гигантское количество данных, но Datatables при построении их все равно будет делить на страницы. Я бы сделал пагинацию и не парил голову ни себе ни другим. Профита от вашего решения грузить все разом никакого

[TanykaGURU]

Константин Б., да, буду делать пагинацию. Спасибо. А поводу профита, ну почему, ежели раз загрузить, то будет сразу вся база, и не надо делать лишних запросов, другой вопрос выдержит ли система такое))

Answer 3

[Антон Антон]

Есть ли в таблицах одинаковые значения, причем в больших количествах? Например пустой телефон? Если, допустим, в каждой таблице 100 пустых телефонов, то в результате будет минимум 1000000 строк

Comments

[TanykaGURU]

Пустые телефоны есть.

[Антон Антон]

Ну в общем, если в выбираемых по `table1`.`name` данных телефон совпадает с кучей строк в table2 и table3, то произойдет умножение количества получаемых строк на количество совпадений. В итоге легко получить выборку из миллионов строк. Думай, как получить данные по другому, если дело в этом.

[TanykaGURU]

Антон Антон, спасибо, убрал с помощью whereNotNull('table1.phone') пустые телефоны, дальше через пагинацию буду делать.