Foreach too slow

Question

I have 2 arrays:

$questions: pid => name
$answers: pid => rid

It inserts in the database all the questions (pid) and if there is an answer (rid) it inserts the answer; if no answer it inserts 0.

foreach($questions as $value) {
    $idanswer = ($answers[$value[pid]]) ? $answers[$value[pid]] : 0;
    $idquestion = $value[pid];
    $sql = "INSERT INTO solucion ( rid, pid) VALUES ( '$idanswer ', '$idquestion ')";
    $db - > query($sql);
}

In my inexperience, I use foreach for almost everything, but in this case too slow. Any advice?

Answer 1

Instead of wrapping a single INSERT query in a PHP loop, use a PHP loop to build up a single INSERT query in a string, then execute that single query at the end.

Hint: INSERT can insert multiple rows at the same time.

Although a single INSERT is fast, the combined round-trip of a MySQL query is always going to scale poorly because you are repeating the connection/communication/interpretation/file-access overhead for each of your N array elements... all of which is needless.