Friday, November 29, 2013

Increase MySQL SELECT speed in complicated statement



I have following MySQL statement using 4 SELECT loops:
  $sql = "SELECT subject_id,study_code,active,reminders FROM subjects WHERE subject_id IN(
               SELECT subject_id FROM review_slot_subjects WHERE review_slot_id
                  IN (SELECT review_slot_id FROM review_slots WHERE project_id=$pid AND review_slot_id
                     IN (SELECT review_slot_id FROM review_slot_reviewers WHERE member_id=$rid))) ORDER BY study_code";
      $result = mysql_query($sql, $db);

It takes  about 2 minutes to finish execution.
Change the MySQL statement into two steps: one is three SELECT, another one SELECT
//step 1
$sql = "SELECT subject_id FROM review_slot_subjects WHERE review_slot_id
                  IN (SELECT review_slot_id FROM review_slots WHERE project_id=$pid AND review_slot_id
                     IN (SELECT review_slot_id FROM review_slot_reviewers WHERE member_id=$rid))";
        $result = mysql_query($sql, $db);
       $subject_id_array=array();
    while($row = mysql_fetch_assoc($result))  $subject_id_array[]=$row['subject_id'];
 //step 2
    $matches = implode(',',$subject_id_array);        
    $sql = "SELECT subject_id,study_code,active,reminders FROM subjects WHERE subject_id IN(
               $matches) ORDER BY study_code";
      $result = mysql_query($sql, $db);


The MySQl execution time is reduced to few seconds.

No comments:

Post a Comment