有效地批量查询数据库

Question

I have a php page that takes 2.8 seconds to render. The page contains a script that reads a txt file via file() line by line (~5000 lines) via a foreach loop. This works perfectly and allows me to wrap each line in a <div>. This all looks something like this.

$text_file = 'path/to/my/text/file.txt';
$lines = file($text_file);
$output = '';
foreach($lines as $line_num => $line){
  $output .= '<div id="'.$line_num.'" class="line">'.htmlspecialchars($line).'</div>'."
";
}
echo $output;

The problem is I need to query if a line number is in the database and if it is give it an extra class highlight. This is what is making the page render so slowly. Each line (~5000) is querying the database within the loop. This looks something like this.

foreach($lines as $line_num => $line){
  // codeigniter is being used here
  $line_exists = $this->line_model->lookup_line($line_num);
  // $line_exists checks the database if the $line_num exists it will return true / false
  if($line_exists){          
    $lines_output .= '<div id="'.$line_num.'" class="line highlight">'.htmlspecialchars($line).'</div>'."
";
  }else{
    $lines_output .= '<div id="'.$line_num.'" class="line">'.htmlspecialchars($line).'</div>'."
";
  }
}

My question is: Is there a more efficient / faster way to do this?

Answer 1

I am not sure exactly what that function checks in the database but one idea to speed things up might be to get the line numbers in database BEFORE the foreach loop. Get all the line numbers in an array, inside the loop just check if the line number is in the array , if it is then use the highlight otherwise the normal case. This way you only query the database once instead of ~5k times.