我插入数据表的循环非常慢

Question

这是代码点火器中面临的问题。

使用数据库mariadb

我有7张叫的桌子

1. 术语
2. 类
3. 截面
4. 主题
5. 试卷6作业_主题7标记并且查询是

<?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; ?>

问题是，插入需要3个多小时，插入后会使页面非常慢。

Answer 1

• 要避免在每个INSERT之前出现SELECT，请使用INSERT IGNORE ... orINSERT。。。正在进行重复密钥更新`
• 为了使任何插入件加速(最多10倍(；批次"；也就是说，在一个查询中插入100行
• 如果数据来自其他表，那么使用INSERT...SELECT...;，这样就可以一次完成所有操作

Answer 2

您可以在插入查询中使用多值来加快插入速度，并且为了提高从数据库加载数据的速度，您应该使用名为index的功能。