100 rows takes about 20 seconds to list

Question

I am creating an in-house patient calendar, the clinic has more than 40K patients in the database.

I am trying to list around 9000 rows but it is taking extremely long time, I tried with 100 rows, it takes around 20 second, how can I make it much faster?

Here is my code:

$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 = "<a href='/apps/patients/patient-file.php?patientid=".$row['id']."#treatment_finance' target='_blank'><img src='/assets/images/Letter-T-blue-icon.png' width='24' height='24'></a>";

    $btn2 = "<a href='/apps/patients/patient-file.php?patientid=".$row['id']."#patient_information' target='_blank'>".$row['first_name']." ".$row['last_name']."</a>";

    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 = "<div class='checkbox checkbox-primary'><input id='checkboxPatients".$row['id']."' class='styled checkAllPatients' type='checkbox' name='checkAllPatients[]' value='".$row['id']."'><label for='checkboxPatients".$row['id']."'></label></div>";

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

use indexing on such columns which taking time. Use EXPLAIN and check which column require indexing. — RJParikh, Apr 22 '16 at 06:00
Since the question is tagged in Mysql category, I'll answer you from database point of view. Create Index on column you're querying, or the primary key column if you have in table. and see the result. — Priyanshu, Apr 22 '16 at 06:00
Learn about `JOIN`; that should get rid of the loop. Don't use `mysql_*` API; switch to `mysqli_*`. — Rick James, Apr 23 '16 at 03:49

score 1 · Answer 1 · edited Apr 22 '16 at 06:17

1

use indexing in your table, index those fields which are using in where clause.
instead of use select * specify column name
instead of interacting database multiple type,fetch data from database at a time then using php show the data.

edited Apr 22 '16 at 06:17

kamal pal

4,187
5
25
40

answered Apr 22 '16 at 06:05

Dipanwita Kundu

1,637
1
9
14

Can you give me an example of how to create indexing? Thanks @Dipanwita Kundu – adams Apr 22 '16 at 06:08
please check this link for indexing: 1. http://www.tutorialspoint.com/mysql/mysql-indexes.htm 2. http://stackoverflow.com/questions/3567981/how-do-mysql-indexes-work – Dipanwita Kundu Apr 22 '16 at 06:10
http://www.informit.com/articles/article.aspx?p=377652 http://stackoverflow.com/questions/1108/how-does-database-indexing-work – Dipanwita Kundu Apr 22 '16 at 06:12

score 1 · Accepted Answer · answered Apr 22 '16 at 06:20

You need to make index to clinic_id and first_name.

See this document http://www.w3schools.com/sql/sql_create_index.asp will help

You using too many queries in this code. reduce query will make better. use join to joining multiple table information to once, with conditions. http://www.w3schools.com/sql/sql_join_left.asp
set names utf8 only need once per code. If problem to fetching other language, checking my.cnf first to define default character set with server and connection.

score 0 · Answer 3 · answered Apr 22 '16 at 06:07

0

Try not using SELECT *.... instead give names of column(s) that you require and create Index on those column(s).
Warning: Just because indexing helps don't create index on all the columns cause index uses extra space

answered Apr 22 '16 at 06:07

Kainix

1,186
3
21
33

And extra time to restructure each index when inserting / deleting rows. – Rocki Apr 22 '16 at 06:20

score 0 · Answer 4 · answered Apr 22 '16 at 06:15

0

Check how to give indexing. I have attached image for you.

Syntax

ALTER TABLE `Table_name` ADD INDEX ( `Column_name` ) ;

answered Apr 22 '16 at 06:15

RJParikh

4,096
1
19
36

score 0 · Answer 5 · answered Apr 22 '16 at 06:18

Following are my suggestions:

1) You have 11 select queries. Out of it, 10 are in the loop and at least half of them run in the loop.

2) It means for every records in the top query, you are running 6 queries. For 100 rows, it is 600 queries (600 times database interaction). Therefore, your page is taking 20 seconds at least.

3) Again, every SQL has SELECT *, this takes more time as database tables may have number of fields.

Solution

1) Every SQL has SELECT *, use only required fields.

2) Try not to fire query in the loop.

3) Instead, get all records before loop and access them by key value array.

4) For example, the sql: $q = "SELECT * FROM dg_users WHERE id = {$row['saved_by']}";

You can have an array of all users in array with key as user id and value as user name

And in the loop, get the value from this array like:

$savedby = isset($users[$row['saved_by']]) ? $users[$row['saved_by']] : '';

Repeat the same for all queries in the loop.

Hope, it will help you.

100 rows takes about 20 seconds to list

5 Answers5