Slow performance while fetching data

Question

My website is performing very slow and taking lot of time to load the data. Its like 2-3 mins to load the data. Can you please suggest me how to make it fast. I am fetching data from multiple table. The database has many entries almost 25000 entries.

Below Is the Code I am currently using.

<table class="table table-striped table-bordered bootstrap-datatable datatable">
<tbody>
  <?php // get all state
     $sql=" SELECT bm.bank_name,b.bank_ifsc,e.emp_id,e.emp_code,
                      e.first_name,e.middle_name,e.last_name,
                      e.active_status as emp_status,
                      e.account_no FROM tblemployee e 
        Left Join tblbank_mst bm  on bm.bank_id=e.fk_bank_id
        Left Join tblbank b  on b.bank_ifsc_id=e.fk_bank_ifsc_id 
        where e.del_status=0
          and e.role_id=4
          and e.is_admin=0 ";
     if($_SESSION["loggedin_role_id"]==2)
     {
     $sql.=" and  e.added_by=".$_SESSION["loggedin_emp_id"];
     }
     $sql.=" order by first_name";

     // echo $sql; exit;

     if(mysql_num_rows($result = mysql_query($sql))>0)
     { 
     while($row = mysql_fetch_array($result))
     { ?>
  <tr>
     <td><?php echo $row['emp_code'];?> </td>
     <td><?php echo $row['first_name'].' '.$row['middle_name'].' '.$row['last_name'];?> </td>
     <td><?php echo $row['bank_name'];?> </td>
     <td><?php echo $row['account_no'];?> </td>
     <td><?php echo $row['bank_ifsc'];?> </td>
     <td class="center">
        <?php if($row['emp_status']==1){ ?>
        <span class="label label-success">Active</span>
        <?php }else{?>
        <span class="label label-danger">Inactive</span>
        <?php }?>
     </td>
     <td class="center">
        <!--<a class="btn btn-success" href="#">
           <i class="halflings-icon white zoom-in"></i>  
           </a>-->
        <?php if($row['approve_status']==0){ ?>
        <a class="btn btn-info" href="edit_employee.php?emp_id=<?php echo $row['emp_id'];;?>">
        <i class="halflings-icon white edit"></i>  
        </a>
        <a class="btn btn-danger" href="#" onClick="ConfirmDelete(<?php echo $row['emp_id'];?>)">
        <i class="halflings-icon white trash"></i> 
        </a>
        <?php }else{ echo "--"; }?>
     </td>
  </tr>

Answer 1

Assuming your largest table is tblemployee, try creating a compound index on the three columns mentioned in your WHERE clause, which is:

 WHERE e.del_status=0 and e.role_id=4 and e.is_admin=0

You can do this with

 CREATE INDEX emp_del_role_admin 
           ON tblemployee
              (del_status, role_id, is_admin);

Why does this help? Because MySQL's query planner can random-access the index to find the first row of your table matching your WHERE statement, then it can read the index sequentially to find the rest of the matching rows.

Of course, if your WHERE filter matches many thousands of rows in your table, you will still have a slow page; it takes time to load, transmit, and render a very large page.

Answer 2

If you are trying to display 25000 (or too many) entries in one time :

This is slow and that's normal, you should paginate your results : use some infinite scroll plugin to display the same thing and limiting results for each query -EDIT : or DataTable pagination options-.

If you are not

First you should have a look at slow queries , configuration to change in my.cnf file. If your query is slow, you may then optimize your query by adding INDEX where you have to. You should use EXPLAIN (documentation) to help you doing this. Be sure you have foreign keys declared as foreign keys (bm.bank_id & e.fk_bank_id - b.bank_ifsc_id & e.fk_bank_ifsc_id), that will speed up your query. Adding index on things like e.role_id could do it aswell.

Only you can know which index to add in this case.

Answer 3

Do not use PHP's mysql_* interface; switch to either mysqli_* or PDO.

Add two composite indexes:

(del_status, role_id, is_admin, first_name)
(del_status, role_id, is_admin, added_by, first_name)

The first handles the case where you skip the additional AND.

The following pattern may help performance more: Change

... bm.bank_name,
...
    Left Join  tblbank_mst bm  ON bm.bank_id=e.fk_bank_id

to

... ( SELECT bank_name FROM tblbank_mst WHERE bank_id=e.fk_bank_id
    ) AS bank_name,
...

Ditto for the other LEFT JOIN.

Answer 4

Please see this answer as it might help you: https://stackoverflow.com/a/35757120/1276062

In short you should check if you have indices on theese columns:

• tblbank_mst.bank_id
• tblemployee.fk_bank_id
• tblbank.bank_ifsc_id
• tblemployee.fk_bank_ifsc_id
• tblemployee.del_status
• tblemployee.role_id
• tblemployee.is_admin
• tblemployee.added_by
• first_name

If the indices won't help you should run EXPLAIN on the query and post the results into the question