Die Auflistung von 100 Zeilen dauert etwa 20 Sekunden ⇐ Php

[Anonymous]

Ich erstelle einen internen Patientenkalender, die Klinik hat mehr als 40.000 Patienten in der Datenbank.
Ich versuche, etwa 9000 Zeilen aufzulisten, aber es dauert extrem lange. Ich habe es mit 100 Zeilen versucht, es dauert etwa 20 Sekunden. Wie kann ich es viel schneller machen?
Hier ist mein Code:

Code: Select all

$getPatients = $db->query("set names 'utf8'");
$q = "SELECT id, p_type, clinic_id, recommended_doctor, hdyhau, partnership_companies, p_auto_control_date, first_name, last_name, company, mobile, p_city, p_state, p_country, saved_by  FROM dg_patients_patients WHERE clinic_id = {$defaultClinic} ORDER BY first_name ASC LIMIT 100";
$getPatients = $db->query($q);

$patientList = "";

while ($row = mysql_fetch_array($getPatients)) {

//Get Patient Type
$getPatientType = $db->query("set names 'utf8'");
$q = "SELECT * FROM dg_patient_type WHERE id = {$row['p_type']}";
$getPatientType = $db->query($q);
$patientType = mysql_fetch_array($getPatientType);

//Get Partnership Company
if($row['partnership_companies'] != '' && $row['partnership_companies'] > 0) {
$getPC = $db->query("set names 'utf8'");
$q = "SELECT * FROM dg_partnership_companies WHERE id = {$row['partnership_companies']}";
$getPC = $db->query($q);
$pc = mysql_fetch_array($getPC);
$pcname = $pc['pc_name'];
} else {
$pcname = '';
}

if(!empty($row['saved_by'])){
//Get User
$getUser = $db->query("set names 'utf8'");
$q = "SELECT * FROM dg_users WHERE id = {$row['saved_by']}";
$getUser = $db->query($q);
$user = mysql_fetch_array($getUser);

$savedby = $user['first_name'];
} else {
$savedby = '';
}

//Get Total Appointments
$q1 = "SELECT * FROM dg_appointments WHERE (appointment_type = 1 OR appointment_type =2 ) AND patient_id = {$row['id']}";
$getApps = $db->query($q1);
$totalAppointments = mysql_num_rows($getApps);

//Get Latest Appointment Date
$q11 = "SELECT * FROM dg_appointments WHERE appointment_status = 4 AND patient_id = {$row['id']} ORDER BY start_date DESC, start_time DESC LIMIT 1";
$getLastesApp = $db->query($q11);
$lastesApp = mysql_fetch_array($getLastesApp);

//Get Clinic
$getClinic = $db->query("set names 'utf8'");
$q = "SELECT * FROM dg_clinics WHERE id = {$row['clinic_id']}";
$getClinic = $db->query($q);
$clinic = mysql_fetch_array($getClinic);

//Get Doctor
if($row['recommended_doctor'] != '' && $row['recommended_doctor'] > 0) {
$getDoctor = $db->query("set names 'utf8'");
$q = "SELECT * FROM dg_users WHERE department = 2 AND id = {$row['recommended_doctor']}";
$getDoctor = $db->query($q);
$doctor = mysql_fetch_array($getDoctor);
$doctorID = $doctor['first_name'].' '.$doctor['last_name'];
} else {
$doctorID = '-';
}

//Get HDYHAU
if($row['hdyhau'] != '' && $row['hdyhau'] > 0){
$q = "SELECT * FROM dg_hdyhau WHERE id = {$row['hdyhau']}";
$getHDYHAU = $db->query($q);
$HDYHAU = mysql_fetch_array($getHDYHAU);
$HDYHAUID = $HDYHAU['hdyhau_name'];
} else {
$HDYHAUID = '-';
}

//Get Country
if($row['p_country'] != '' && $row['p_country'] > 0){
$getCountry = $db->query("set names 'utf8'");
$sql = "SELECT * FROM dg_ulke WHERE Id = {$row['p_country']}";
$getCountry = $db->query($sql);
$country = mysql_fetch_array($getCountry);
$countryID = $country['tr_TR'];
} else {
$countryID = '-';
}

//Get Cities
if($row['p_state'] != '' && $row['p_state'] > 0){
$getState = $db->query("set names 'utf8'");
$sql = "SELECT * FROM dg_il WHERE Id = {$row['p_state']}";
$getState = $db->query($sql);
$state = mysql_fetch_array($getState);
$stateID = $state['IlAdi'];
} else {
$stateID = '-';
}

//Get Streets
if($row['p_city'] != '' && $row['p_city'] >  0){
$getCity = $db->query("set names 'utf8'");
$sql = "SELECT * FROM dg_ilce WHERE Id = {$row['p_city']}";
$getCity = $db->query($sql);
$city = mysql_fetch_array($getCity);
$cityID = $city['IlceAdi'];
} else {
$cityID = '-';
}

$btn1 = "[url=/apps/patients/patient-file.php?patientid=]
[img]/assets/images/Letter-T-blue-icon.png[/img]
[/url]";

$btn2 = "[url=/apps/patients/patient-file.php?patientid=]".$row['first_name']." ".$row['last_name']."[/url]";

if($lastesApp['start_date']){
$latestAppDate = date('d.m.Y', strtotime($lastesApp['start_date']));
} else {
$latestAppDate = '-';
}

if($row['p_auto_control_date'] != '' && $row['p_auto_control_date'] != '0000-00-00'){
$pacd = date('d.m.Y', strtotime($row['p_auto_control_date']));
} else {
$pacd = '-';
}

$btn5 = "";

$patientList .= "";
$patientList .= "".$btn5."";
$patientList .= "".$clinic['clinic_name']."";
$patientList .= "".$btn1."";
$patientList .= "".$btn2."";
$patientList .= "".$row['mobile']."";
$patientList .= "".$cityID."";
$patientList .= "".$stateID."";
$patientList .= "".$row['company']."";
$patientList .= "".$pcname."";
$patientList .= "".$totalAppointments."";
$patientList .= "".$latestAppDate."";
$patientList .= "".$pacd."";
$patientList .= "".$savedby."";
$patientList .= "".$doctorID."";
$patientList .= "".$HDYHAUID."";
$patientList .= "".$countryID."";
$patientList .= "";
}
echo $patientList;