title: Optimizing the SQL Query to get data from large amount MySQL database
body: <p>I am having a problem getting data from a large amount MySQL database. </p>
  
  <p>With the below code it is ok to get the list of 10K patients and 5K appointments which is our test server. </p>
  
  <p>However, on our live server, the number of patients is over 100K and the number of appointments is over 300K and when I run the code after a while it gives 500 error.</p>
  
  <p>I need the list of the patients whose patient_treatment_status is 1 or 3 and has no appointment after one month from their last appointment. (The below code is working for small amount of patients and appointments)</p>
  
  <p>How can I optimise the first database query so there will be no need the second database query in the foreach loop?</p>
  
  <pre><code>&lt;?php
  ini_set('memory_limit', '-1');
  ini_set('max_execution_time', 0);
  
  require_once('Db.class.php');
  
  $patients = $db-&gt;query("
  SELECT
      p.id, p.first_name, p.last_name, p.phone, p.mobile,
      LatestApp.lastAppDate
  FROM
      patients p
  LEFT JOIN (SELECT patient_id, MAX(start_date) AS lastAppDate FROM appointments WHERE appointment_status = 4) LatestApp ON p.id = LatestApp.patient_id
  WHERE
      p.patient_treatment_status = 1 OR p.patient_treatment_status = 3
  ORDER BY
      p.id
  ");
  
  foreach ($patients as $row) {
      $one_month_after_the_last_appointment = date('Y-m-d', strtotime($row['lastAppDate'] . " +1 month"));
      $appointment_check = $db-&gt;single("SELECT COUNT(id) FROM appointments WHERE patient_id = :pid AND appointment_status = :a0 AND (start_date &gt;= :a1 AND start_date &lt;= :a2)", array("pid"=&gt;"{$row['id']}","a0"=&gt;"1","a1"=&gt;"{$row['lastAppDate']}","a2"=&gt;"$one_month_after_the_last_appointment"));
  
      if($appointment_check == 0){
          echo $patient_id = $row['id'].' - '.$row['lastAppDate'].' - '.$one_month_after_the_last_appointment. '&lt;br&gt;';
      }
  }
  ?&gt;
  </code></pre>