my loop for inserting data table is very slow-

Question

this is the problem facing in codeigniter.

using database mariadb

i have 7 table called

1. terms
2. class
3. section
4. subject
5. exam_paper 6 assign_subject 7 mark and the query is

  <?php
            $select  =   $this->db->get_where('terms', array())->result_array();

            foreach ($select as $key => $data_select) :
                $verify_data1 = array(
                    'session_year_id' => $session_year_id
                );
                $query1 = $this->db->get_where('class', $verify_data1);
                $select1 = $query1->result_array();

                foreach ($select1 as $key => $data_select1) :
                    $verify_data2 = array(
                        'class_id' => $data_select1['class_id'],
                        'session_year_id' => $session_year_id
                    );
                    $query2 = $this->db->get_where('section', $verify_data2);
                    $select2 = $query2->result_array();

                    foreach ($select2 as $key => $data_select2) :
                        $verify_data3 = array(
                            'class_id' => $data_select1['class_id'],
                            'section_id' => $data_select2['section_id'],
                            'session_year_id' => $session_year_id
                        );
                        $query3 = $this->db->get_where('subject', $verify_data3);
                        $select3 = $query3->result_array();

                        foreach ($select3 as $key => $data_select3) :
                            $verify_data4 = array(
                                'terms_id' => $data_select['terms_id'],
                                'class_id' => $data_select1['class_id'],
                                'section_id' => $data_select2['section_id'],
                                'subject_id' => $data_select3['subject_id'],
                                'session_year_id' => $session_year_id

                            );
                            $query4 = $this->db->get_where('exam_paper', $verify_data4);
                            $select4 = $query4->result_array();

                            foreach ($select4 as $key => $data_select4) :
                                $verify_data5 = array(
                                    'class_id' => $data_select1['class_id'],
                                    'section_id' => $data_select2['section_id'],
                                    'session_year_id' => $session_year_id

                                );
                                $query5 = $this->db->get_where('assign_subject', $verify_data5);
                                $select5 = $query5->result_array();

                                foreach ($select5 as $key => $data_select5) :
                                    $verify_data6 = array(
                                        'terms_id' => $data_select['terms_id'],
                                        'class_id' => $data_select1['class_id'],
                                        'section_id' => $data_select2['section_id'],
                                        'subject_id' => $data_select3['subject_id'],
                                        'exam_paper_id' => $data_select4['exam_paper_id'],
                                        'student_id' => $data_select5['student_id'],
                                        'status ' => 1,
                                        'session_year_id' => $session_year_id

                                    );
                                    $query = $this->db->get_where('mark', $verify_data6);
                                    $select6 = $query->result_array();

                                    $sql = "select * from mark order by mark_id desc limit 1";
                                    $return_query = $this->db->query($sql)->row()->mark_id + 1;
                                    $verify_data6['mark_id'] = $return_query;

                                    if ($query->num_rows() < 1)
                                        $this->db->insert('mark', $verify_data6);

                                endforeach;
                            endforeach;
                        endforeach;
                    endforeach;
                endforeach;
            endforeach; ?>

the problem is that it is takes more than 3 hour to insert and after inserting it makes page very slow.

Answer 1

• To avoid the SELECT before each INSERT, use INSERT IGNORE ... or INSERT...ON DUPLICATE KEY UPDATE...`
• To speed up any insert (by up to 10-fold), "batch" it. That is insert, say, 100 rows in a single query.
• If the data is coming from other tables, then use INSERT...SELECT...; so you can do it all at once.

Answer 2

you can use multi value in insert query to make insert faster and for better speed in loading data from database you should use feature called index .