Как ускорить обработку запроса к бд?

Question

[GrimJack]

Есть таблица lookups.
В ней хранятся записи структурированные по record_id
Структурировал так:

$lookups = (new Lookup)
            ->query()
            ->select([
                'lookup_id',
                'record_id',
                'lookup_list_id',
                'linked_record_id',
                'data',
                'element_label',
                'field_name',
                'sequence_order',
                'field_no',
                'archive'
            ])
            ->where('company_id', UserData::getDataUser()->company_id)
            ->where('lookup_list_id', $lookup_list_id)
            ->orderBy('sequence_order')
            ->get()
            ->groupBy(function($item, $key) {
                return $item->record_id;
            });

Все работает и достаточно быстро и выглядит примерно так:

Где 13964 это record_id, а внутри все элементы с таким record_id
Теперь есть записи у которых есть связь в виде linked_record_id
У 13964 это 13897
То есть внутри 13964 должен появиться массив с объектами внутри. Объекты это записи с record_id = 13897
Как правильно построить запрос?
Сейчас при ~900 записях в таблице такой запрос выполняется около 1-2 минут

$lookups = (new Lookup)
            ->query()
            ->select([
                'lookup_id',
                'record_id',
                'lookup_list_id',
                'linked_record_id',
                'data',
                'element_label',
                'field_name',
                'sequence_order',
                'field_no',
                'archive'
            ])
            ->where('company_id', UserData::getDataUser()->company_id)
            ->where('lookup_list_id', $lookup_list_id)
            ->orderBy('sequence_order')
            ->get()
            ->groupBy(function($item, $key) {
                if($item->linked_record_id > 0)
                {
                    $link = (new Lookup)
                        ->query()
                        ->select([
                            'lookup_id',
                            'record_id',
                            'lookup_list_id',
                            'linked_record_id',
                            'data',
                            'element_label',
                            'field_name',
                            'sequence_order',
                            'field_no',
                            'archive'
                        ])
                        ->where('company_id', UserData::getDataUser()->company_id)
                        ->where('record_id', $item->linked_record_id)
                        ->orderBy('sequence_order')
                        ->get()->toArray();
                    $item->linked_data = $link;
                }
                    return $item->record_id;
            });

Ну и выполняется не совсем правильно, он вписывает данные внутрь каждого элемента, а не внутри 13964
Как правильно перестроить запрос? Второй день голову ломаю, ничего не приходит на ум(

UPD: Вот такой код выполняется 27секунд (вполне приемлемо для количества запросов) для 97 record_id + 1 запрос для каждого record_id

spoiler

$lookups = (new Lookup)
            ->query()
            ->select([
                'lookup_id',
                'record_id',
                'lookup_list_id',
                'linked_record_id',
                'data',
                'element_label',
                'field_name',
                'sequence_order',
                'field_no',
                'archive'
            ])
            ->where('company_id', UserData::getDataUser()->company_id)
            ->where('lookup_list_id', $lookup_list_id)
            ->orderBy('sequence_order')
            ->get()
            ->groupBy(function($item, $key) {
                /*if($item->linked_record_id > 0)
                {
                    $link = (new Lookup)
                        ->query()
                        ->select([
                            'lookup_id',
                            'record_id',
                            'lookup_list_id',
                            'linked_record_id',
                            'data',
                            'element_label',
                            'field_name',
                            'sequence_order',
                            'field_no',
                            'archive'
                        ])
                        ->where('company_id', UserData::getDataUser()->company_id)
                        ->where('record_id', $item->linked_record_id)
                        ->orderBy('sequence_order')
                        ->get()->toArray();
                    $item->linked_data = $link;
                }*/
                return $item->record_id;
            });
        $records = [];
        foreach ($lookups as $record_id => $node)
        {
            $record = [];
            foreach ($node as $lookup)
            {
                $record['data'] = $node;
                if($lookup->linked_record_id > 0)
                {
                    $link = (new Lookup)
                        ->query()
                        ->select([
                            'lookup_id',
                            'record_id',
                            'lookup_list_id',
                            'linked_record_id',
                            'data',
                            'element_label',
                            'field_name',
                            'sequence_order',
                            'field_no',
                            'archive'
                        ])
                        ->where('company_id', UserData::getDataUser()->company_id)
                        ->where('record_id', $lookup->linked_record_id)
                        ->orderBy('sequence_order')
                        ->get()->toArray();
                    $record['link_data'] = $link;
                    break;
                }
            }
            $records[$record_id] = $record;
        }

Но теперь смущает break в цикле. Как можно более корректно это организовать?

Answer 1

[Иван Корюков]

Что касается производительности, то вы столкнулись с проблемой N+1 запроса.
Это когда вам надо получить список записей и для каждой из них вы делаете дополнительный запрос к БД. 900 записей? Ок, будет первый запрос + ещё 900, по одному на каждую запись.
Быстрее может быть выполнить всего два запроса. Первый берёт записи, а второй берёт все дополнительные данные сразу для всех записей и потом уже на стороне php вы в цикле засовываете данные в каждую запись.
Вот абстрактный пример:

$shops = query("Select * from shop where city='Moscow'");
$shops = indexBy("id",$shops);
$employes = query("Select * from employe join shop on(shop.id = employe.id_shop) where shop.city='Moscow'");
foreach($employes as $eml){
    $shops[$emp["id_shop"]]["employes"][] = $emp;
}

Comments

[GrimJack]

Спасибо, тоже дошел до этой мысли, но вот столкнулся с новой проблемой, описал в upd

Answer 2

[Adamos]

Вам нужен список записей, у каждой из которых есть список связанных записей?
Это делается двумя запросами:
1. Загрузить нужные записи.
1.1. В РНР пройти по ним и собрать id связанных записей.
2. Загрузить все записи со связанными id.
2.1. В РНР пройти по всем записям и приписать им подходящие связанные.
И все. Никаких запросов в цикле и многосекундного дерганья одной жалкой тысячи записей.

Собственно, если в Ларавеле прописать связь этого поля с той же таблицей и использовать lazy load, он, по-моему, вполне самостоятельно обходится теми же двумя запросами...

Answer 3

[GrimJack]

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

$linkeds = [];
        $lookups = (new Lookup)
            ->query()
            ->select([
                'lookup_id',
                'record_id',
                'lookup_list_id',
                'linked_record_id',
                'data',
                'element_label',
                'field_name',
                'sequence_order',
                'field_no',
                'archive'
            ])
            ->where('company_id', UserData::getDataUser()->company_id)
            ->where('lookup_list_id', $lookup_list_id)
            ->orderBy('sequence_order')
            ->get()
            ->groupBy(function($item, $key) use (&$linkeds) {
                if($item->linked_record_id > 0)
                {
                    $linkeds[$item->record_id] = $item->linked_record_id;
                }
                return $item->record_id;
            });
        $records = [];
        foreach ($lookups as $record_id => $node)
        {
            $records[$record_id]['data'] = $node;
            if(isset($linkeds[$record_id]))
            {
                $link = (new Lookup)
                    ->query()
                    ->select([
                        'lookup_id',
                        'record_id',
                        'lookup_list_id',
                        'linked_record_id',
                        'data',
                        'element_label',
                        'field_name',
                        'sequence_order',
                        'field_no',
                        'archive'
                    ])
                    ->where('company_id', UserData::getDataUser()->company_id)
                    ->where('record_id', $linkeds[$record_id])
                    ->orderBy('sequence_order')
                    ->get()->toArray();
                $records[$record_id]['link_data'] = $link;
            }
        }