Почему запрос к mysql внутри цикла while выполняется очень долго?

Question

[Paul14]

Время получения 10000 записей о из таблицы clients = 0.08s
Время получения 40000+ записей из таблицы offers = 0.2s

Я хочу пробегаясь по каждому клиенту, вычленить его ID и потом вытащить из таблицы offers все строки с clientID = ID.

В JSON чтобы получить упрощенно такую структуру:

[{
client:{...},
offers:[{...},{...}]
},
{
client:{...},
offers:[{...},{...}]
}]

Вот такой код отрабатывается за 4s. Почему так долго то. Что я делаю не так?

...
	$result = $connection->query($sql);	
	$return = array("contents" => array());	

	if($result->num_rows>0){
		while($row = $result->fetch_assoc()){
		    
		    $return_x = array("client" => array(), 'offers' => array());
		    array_push($return_x['client'],$row);
			
			$sql_calls = "SELECT * FROM offers WHERE clientID = ".$row['id'];
			$result_calls = $connection->query($sql_calls);
			if($result_calls->num_rows>0){
		            while($row_calls = $result_calls->fetch_assoc()){
		                array_push($return_x['offers'],$row_calls);
		            }
			}			
			
			array_push($return['contents'],$return_x);
			
			
		}
	}	
	
	echo json_encode(array(	"result" => 'true',	"array_result" => $return));
	$connection->close();
}

Answer 1

[nokimaro]

Время получения 40000+ записей из таблицы offers = 0.2s

Вот такой код отрабатывается за 4s

то есть в цикле делается 40тыс запросов к таблице clients каждый из который выполняется за 0.0001 sec

вам точно надо прочитать про JOIN'ы чтобы одним запросом получить все нужные данные
https://www.w3schools.com/sql/sql_join.asp

Comments

[Paul14]

SELECT * FROM offers AS a
  LEFT JOIN clients AS b
    ON a.clientID = b.id
   	LIMIT 10

Я получаю объединенную строку данных клиента и данных оффера, а я хочу прийти к такой структуре

array(	
        "client" => {...} ,	
        "offers" => array({...},{...})
        )

[AUser0]

Paul14, во-первых, благодаря array_push($return_x['client'],$row) вы получаете немного другую структуру

array(	
        "client" => array({...} ),
        "offers" => array({...},{...})
        )

Во-вторых, в полученных JOIN-ом данных найти данные клиента, и собрать из эти данных нужный массив - ну просто проще простого, как нечего делать, ей-богу!

[Rsa97]

Борис Алексеев,

в mysql нет замечательной функции ArrayAgg из постгрес,

С чего вы это взяли? JSON_ARRAYAGG

[Paul14]

AUser0, можно пример. Для меня это не очевидно, что очень просто. Я не нашел ничего, чтобы сделать так как вы говорите в один джоин. Единственное что приходит на ум, так это сделать запрос к таблице clients, получить данные.
Потом сделать LEFT JOIN запрос где все offers.clientID = clients.id.
То есть получить все поля таблицы offers где будет добавлена колонка id (из clients)

И уже потом в цикле по clients у каждого клиента выдергивать айдишник и искать его среди всех офферов, и формировать нужный ответ.

Вы про это говорите? Или типо можно сделать все одним запросом?

[Paul14]

Борис Алексеев, я думаю, что всегда необходимо получать со стороны сервера уже готовый под задачу структурированный ответ JSON, а на стороне клиента выполнять минимальные действия по его видоизменению. Проблем на клиенте (Реакт приложении) в плане загрузки оперативы я не заметил, даже на маломощных компьютерах, да и база пока маленькая на самом деле.

[nokimaro]

Paul14, Да, получить из базы данные в виде списка и затем сгруппировать на PHP офферы по клиентам чтобы закодировать в json это нормальный подход
В SELECT'e вам по прежнему доступен WHERE ... если нужно получить данные по конкретному офферу или конкретному клиенту
WHERE offers.offerId =
WHERE clients. clientID =

[nokimaro]

<?php
$data = [];
while($row = $result->fetch_assoc()) {
    if(!isset($data[$row['clientId']]['client'])) {
        $data[$row['clientId']]['client'] = [
            'client_id' => $row['clientId'],
            'client_name' => 'итд',
        ];

        $data[$row['clientId']]['offers'] = [];
    }

    $data[$row['clientId']]['offers'][] = [
        'offer_id' => $row['offerId'],
        'offer_name' => 'итд',
    ];
}

echo json_encode([
    "result" => 'true',
    "array_result" => array_values($data),
]);

[nokimaro]

Можно решить вопрос с использованием JSON_ARRAYAGG и сгруппировать данные на уровне MySQL, но это будет означать лишь что часть логики приложения из PHP кода уедет на уровень БД, что не всегда хорошо в поддержке.

[Paul14]

nokimaro, спасибо! Только момент еще: у меня в таблице clients есть поле id. И в таблице offers есть поле id. Как их отделить друг от друга? Или можно как-то их заменить на другое значение, например cliend__id и offer__id?

[nokimaro]

Paul14,

select clients.id as cid, offers.id as oid

Answer 2

[Alexey Marchenko]

Запросы в цикле надо избегать! Иногда бывает, что это трудно сделать, но в вашем случае выглядит легко как уже написал nokimaro используйте JOIN. Примерно так:

SELECT c.id, o.* FROM clients AS c JOIN offers AS o ON c.id = o.clientID WHERE <и тут условия по которым выбираете clients>.

PS. o.* - это плохо, перечисляйте те поля которые вам нужны для работы.
Вот тут хорошая статья про JOIN