Optimise the creation of a record with many records that belong to it

Question

Description

I'm writing full stack web application using ReactJS, Laravel and MySQL that allows users to create quizzes.

My database structure:

Quizzes table

create table quizzes (
    id bigint unsigned auto_increment primary key,
    title varchar(255) not null,
    description text null,
    duration smallint unsigned not null,
    is_open tinyint(1) default 0 not null,
    shuffle_questions tinyint(1) default 0 not null,
    user_id bigint unsigned not null,
    lesson_id bigint unsigned not null,
    created_at timestamp null,
    updated_at timestamp null,
    constraint quizzes_lesson_id_foreign foreign key (lesson_id) references lessons (id) on delete cascade,
    constraint quizzes_user_id_foreign foreign key (user_id) references users (id) on delete cascade
) collate = utf8mb4_unicode_ci;

Questions table

create table questions (
    id bigint unsigned auto_increment primary key,
    title text not null,
    description text null,
    image varchar(255) null,
    type enum ('radio', 'checkbox', 'text', 'image') not null,
    is_required tinyint(1) default 0 not null,
    points tinyint unsigned default '0' not null,
    quiz_id bigint unsigned not null,
    created_at timestamp null,
    updated_at timestamp null,
    constraint questions_quiz_id_foreign foreign key (quiz_id) references webagu_24082021.quizzes (id) on delete cascade
) collate = utf8mb4_unicode_ci;

Answers table

create table answers (
    id bigint unsigned auto_increment primary key,
    value varchar(1024) null,
    is_correct tinyint(1) default 0 not null,
    quiz_id bigint unsigned not null,
    question_id bigint unsigned not null,
    created_at timestamp null,
    updated_at timestamp null,
    constraint answers_question_id_foreign foreign key (question_id) references questions (id) on delete cascade,
    constraint answers_quiz_id_foreign foreign key (quiz_id) references quizzes (id) on delete cascade
) collate = utf8mb4_unicode_ci;

Data that comes from UI when user press "Save quiz" button

//....
axios
    .post('/quizzes', { "quiz": QuizData, "questions": QuestionsData, "answers": AnswersData })
    .then(res => {
        if(201 === res.status) alert('Quiz saved!');
        console.log(res.data)
    });
//....

Quiz controller store method

public function store(Request $request): JsonResponse
{
    $quizData = $request->input('quiz');
    $questions = $request->input('questions');
    $answers = $request->input('answers');

    $groupedAnswers = Utils::groupBy('_question_id', $answers);

    //DB::beginTransaction();

    $quizData['user_id'] = \auth('api')->user()->id;
    $quiz = Quiz::create($quizData);

    $new_questions = [];
    $new_answers = [];

    foreach ($questions as $question) {
        $question['quiz_id'] = $quiz->id;
        $new_question = Question::create($question);
        $new_questions[] = $new_question;

        $qid = $question['_question_id'];

        if (isset($groupedAnswers[$qid])) {
            $question_answers = $groupedAnswers[$qid];

            foreach ($question_answers as $answer) {
                $answer['quiz_id'] = $quiz->id;
                $answer['question_id'] = $new_question->id;

                $new_answer = Answer::create($answer);
                $new_answers[] = $new_answer;
            }
        }
    }

    //DB::commit();

    $resData = ['quiz' => $quiz, 'questions' => $new_questions, 'answer' => $new_answers];

    return response()->json($resData, 201);
}

My current code algorithm:

1. Creates Quiz object
2. In foreach loop assign Quiz::id to Question object quiz_id foreign key column and creates
3. In inner foreach loop assign Question::id to Answer object question_id foreign key column and creates

Problem

This algorithm creates Q (questions count) * A (answers count) SQL queries - and this this very slow.

For example, if quiz contains 50 questions, each have 4 answers variants, query will contain 50 * 4 = 200 SQL queries.

So, how to change this bad solution to make it work faster?

Answer 1

Actually if you have 50 questions, you have:

• 1 query to create a quiz
• 50 queries to create questions
• 200 queries to create answers

Total : 251 queries.

---

If I'm not mistaken with your coding, you can optimize your query like this (example 50 questions, I explain in the comment block) :

$input_quiz      = $request->input('quiz');
$input_questions = $request->input('questions');
$input_answers   = $request->input('answers');
$groupedAnswers  = Utils::groupBy('_question_id', $input_answers);

/*********************/

// Create a quiz (1 query)
$quiz = Quiz::create($input_quiz); 

// Create questions (50 queries)
$questions = $quiz->questions()->createMany($input_questions);

// Prepare answers data
$answers = [];

// Loop $questions
foreach ($questions as $key => $question){

    // If I'm not mistaken, the index on the input 
    // will be equal to $questions (starting at 0)
    $qid = $input_questions[$key]['_question_id'];

    if(isset($groupedAnswers[$qid])){
        $question_answers = $groupedAnswers[$qid];

        // Modify answer
        foreach ($question_answers as $_answer){
            $_answer['quiz_id']     = $quiz->id;
            $_answer['question_id'] = $question->id;
            $_answer['created_at']  = now();    // Laravel insert not saved created_at column
            $_answer['updated_at']  = now();    // Laravel insert not saved updated_at column
            $answers[]              = $_answer; // Push it
        }
    }
}

// Then, we will bulk insert using the insert method (1 query)
$answers = Answer::insert($answers);

Now, you have :

• 1 query to create a quiz
• 50 queries to create questions
• 1 query to create answers

Total : 52 queries.

---

What I've ever done in a case like this is, use only 3 queries. But many considerations, such as using a temporary column. However, I don't think you need to go that far.