为什么循环中的查询非常慢

Question

I don't have a enough experience in mysql with php, but am trying to develop a school report card, am trying to query the students, with their exams, subjects and marks. In this case I have three loops. the First loop is for the students I use it to get the student's ID, the second loop is for the exam, i use it to get the exam ID and the last is for subject I use it to get the subject ID, i need all this IDs in order to get the marks for each student per exam per subject. Bellow are the loops;

foreach ($students as $student_row){ //get each student's ID
 $studentID = $student_row['studentID'];
       foreach ($allExams as $allExam) { //get each exam's ID
       $examID = $allExam['examID'];
            foreach ($subjects as $subject){ //get each subject's ID
               $subjectID = $subject['subjectID'];

$obtained_mark_query = $this->db->query("select * from mark
            where mark.studentID = '$studentID' && mark.examID = '$examID' && 
            mark.subjectID = '$subjectID'")->row()->mark; //to get mark for each student per exam per subject
                        }

              }

}

I don't know what's really the problem but this loop takes really long, it takes 26 second to return all the students in a specific class, there are only 23 students in a class, four exams, and five subjects, so am wondering what it'll really be when there are more than those students. Please any idea why it's behaving like this, any idea i can use other than loops.

Answer 1

There are 2 main issues in your code's logic:

1. You are selecting every field with SELECT * FROM which is highly inefficient, try selecting only the fields you are going to use
2. You are querying in your 3rd nested loop, so instead of querying it once, you are doing 1 query per student, per exam, per subject (like 23 * 4 * 5 = 460 queries)

The solution is to refactor your code, your db, and fetch that information in a simple query, then iterate through the returned records and handle the information server side.