php - 如何改进 php 和 MySQL 中的查询以实现快速显示

Question

我有下面两个表:表candidates存储候选人信息，表candidate_subjects存储结果。我想从两个表中查询并将结果显示在网页上

CREATE TABLE candidate_subjects (
  id INT(10) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  candidate_id INT(11),
  exam_type_id INT(10),
  subject_id INT(10),
  ca_score INT(11),
  exam_score INT(6),
  score_grade VARCHAR(10),
  date_created VARCHAR(10),
  date_modified TIMESTAMP
);

INSERT INTO `candidate_subjects` (`id`, `candidate_id`, `exam_type_id`, 
`subject_id`, `ca_score`, `exam_score`, `score_grade`, `date_created`, 
`date_modified`) VALUES
  (1, 2, 1, 32, 22, 61, A, '2017-02-01', '2017-08-28 13:10:33'),
  (2, 2, 1, 5, 21, 38, B, '2017-02-01', '2017-08-28 13:10:33'),
  (3, 2, 1, 14, 21, 51, A, '2017-02-01', '2017-08-28 13:10:33'),
  (4, 2, 1, 1, 19, 34, B, '2017-02-01', '2017-08-28 13:10:33'),
  (5, 2, 1, 2, 23, 39, B, '2017-02-01', '2017-08-28 13:10:33'),
  (6, 2, 1, 38, 20, 32, B, '2017-02-01', '2017-08-28 13:10:33'),
  (7, 2, 1, 53, 24, 47, A, '2017-02-01', '2017-08-28 13:10:33'),
  (8, 4, 1, 32, 19, 61, A, '2017-02-01', '2017-08-28 13:11:27'),
  (9, 4, 1, 5, 22, 41, B, '2017-02-01', '2017-08-28 13:11:27'),
  (10, 4, 1, 14, 20, 46, A, '2017-02-01', '2017-08-28 13:11:27'),
  (11, 4, 1, 1, 23, 37, B, '2017-02-01', '2017-08-28 13:11:27'),
  (12, 4, 1, 2, 21, 36, B, '2017-02-01', '2017-08-28 13:11:27'),
  (13, 4, 1, 38, 22, 34, B, '2017-02-01', '2017-08-28 13:11:27'),
  (14, 4, 1, 53, 24, 52, A, '2017-02-01', '2017-08-28 13:11:27'),
  (15, 5, 1, 32, 20, 62, A, '2017-02-01', '2017-08-28 13:11:44'),
  (16, 5, 1, 5, 22, 38, B, '2017-02-01', '2017-08-28 13:11:44');


CREATE TABLE candidates (
  id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  exam_no VARCHAR(15),
  surname VARCHAR(50),
  other_names VARCHAR(100),
  school_id INT(11),
  registration_completed INT(11),
  exam_scores_completed INT(5),
  remark VARCHAR(10)
);

INSERT INTO candidates (id, exam_no, surname, other_names, school_id,
registration_completed, exam_scores_completed, remark) VALUES
 (1, '1171052001', 'ABADO', 'MASENENGEN', 1052, 1, '1', 'RESIT'),
 (2, '1170938001', 'AGBA', 'NGUHER', 938, 1, '1', 'FAIL'), 
 (3, '1170071001', 'ABEE', 'SESUUR', 71, 1, '1', 'PASS'),
 (4, '1170938002', 'AHEN', 'REBECCA DOOSUUN', 938, 1, '1', 'PASS');

我有一个函数可以获取候选人在candidate_subjects表中某个科目的成绩。

    function getgradebycandsub($candidate_id, $sub){

        $s = "SELECT score_grade FROM `candidate_subjects` WHERE candidate_subjects.candidate_id='$candidate_id' AND candidate_subjects.subject_id='$sub'";

            $r = mysql_query($s);
            $c = mysql_fetch_array($r);
            return $c['score_grade'];
     }

我有以下循环:

      $s = "SELECT id, exam_no, surname, other_names, remark FROM candidates ORDER BY surname";
    $r = mysql_query($s);

echo "<tr><td align='center'>S/N</td><td align='center'>EXAM NO.</td><td align='center'>NAMES OF CANDIDATES</td><td align='center'>ENG</td><td align='center'>MATH</td><td align='center'>B.STD</td><td align='center'>C.ART</td><td align='center'>FREN</td><td align='center'>HAU</td><td align='center'>BST</td><td align='center'>ARAB</td><td align='center'>P.VOC</td><td align='center'>R.N.V</td><td align='center'>REMARK</td></tr>";
 while ($candidates = mysql_fetch_array($r)) {

        echo "<tr>";
        echo "<td height='30'>" . ++$sno . "</td>";
        echo "<td>" . $candidates['exam_no'] . "</td>";
        echo "<td>" . $candidates['surname'] . "&nbsp;&nbsp; " . $candidates['other_names'] . "</td>";
        echo "<td align='center'>".getgradebycandsub($candidates['id'], '1')."</td>";
        echo "<td align='center'>".getgradebycandsub($candidates['id'], '2')."</td>";
        echo "<td align='center'>".getgradebycandsub($candidates['id'], '5')."</td>";
        echo "<td align='center'>".getgradebycandsub($candidates['id'], '14')."</td>";
        echo "<td align='center'>".getgradebycandsub($candidates['id'], '22')."</td>";
        echo "<td align='center'>".getgradebycandsub($candidates['id'], '53')."</td>";
        echo "<td align='center'>".getgradebycandsub($candidates['id'], '32')."</td>";
        echo "<td align='center'>".getgradebycandsub($candidates['id'], '33')."</td>";
        echo "<td align='center'>".getgradebycandsub($candidates['id'], '38')."</td>";
        echo "<td align='center'>".getgradebycandsub($candidates['id'], '27')."</td>";
        echo "<td align='center'>".$candidates['remark']."</td>";
        echo "</tr>";
    }

我的挑战是需要 5 个小时才能显示 4000 条记录。更新
预期输出为 S/No、考试号、姓名、ENG、MATH、B.STD、C.ART、FREN、HAU、BST、ARAB、P.VOC、R.N.V、REMARK。这是考生的详细信息，例如考试号、姓名、科目成绩和备注

Answer 1

使用JOIN而不是function调用将提高性能。因为，在每个函数调用中，您都会再次执行查询，该查询会进入数据库并获取结果，这会导致性能下降。

SELECT score_grade, id, exam_no, surname, other_names, remark 
FROM candidate_subjects cs
INNER JOIN  candidates  ON 
cs.candidate_id=c.id ORDER BY id, subject_id;

它将按学生 ID 和主题返回结果顺序。