ajax grabbing MySQL data is VERY slow....

[mcerveni]

I built a website for my work where over 130 people use to track their phone calls.

At first, calling the functions to grab the mysql data was very quick.

Now? there are over 3000 records in one of my tables and pressing any button to grab data from the database, it just hangs..i have to press it 2 times for it to work... why is that??

 

 

[corbin]

We will need to see code to know.  I would guess either your DB needs indexes or some other optimizations or your PHP coding is bad.

[Mchl]

Start with EXPLAINing your query that gets this data. Show us what it says.

[mcerveni]

one example is a a Fetch Transfers button:

 

ajax code

stat_loader();

timeInterval = setInterval('getTransfers_refresh();', 5000);

getTransfers_refresh();


}


function getTransfers_refresh() {



http.open('get', 'include/getTransfers.php');
http.onreadystatechange = gettransfer;
http.send(null);

function gettransfer() {
if(http.readyState == 4){ 
var response = http.responseText;
document.getElementById('mainContent').innerHTML = ' ' +response+ '';
}


}

}

 

 

 

php code

//DATE FINDER
$month_num= date('m');
$start_year = "2009";
$start_month = $month_num;
$start_day = date('d');
$end_year = "2009";
$end_month = $month_num;
$end_day = date('d');


$start = $start_year . "-" . $start_month ."-".$start_day;
$end = $end_year . "-" . $end_month ."-".$end_day;


mysql_select_db($agentcenter_database);
mysql_query("USE agentcenter");


$sql = "SELECT * FROM transfer WHERE date BETWEEN '$start' AND '$end' ORDER BY time DESC";
$result = mysql_query($sql);

$num_rows = mysql_num_rows($result); 



?>



<span id="updateDay" >
  
  <div align="center"> <h2> Transfers </h2> </div>





<?php showTotalIssues_transfers(); ?> 




<div align="left"> 
  Total # of Transfers: <?php echo $num_rows; ?> 
</div> <br>


<table id="hor-minimalist-b" summary="Employee Pay Sheet" style="width:820px">
    <thead>
<tr> <th>  Agent Name  </th> <th>  CA Number  </th>  <th>  Ticket ID</th> <th> Time </th>  
   <th>  Transfer To </th>  <th>  Reason </th>   <th> Approved By </th>

  </tr>
    </thead>

<?php while($row= mysql_fetch_array($result)) { //loop record. 

?>



     <tbody>
<tr> <td> <?php echo $row['agentName'];?>  </td> <td> <?php echo $row['empID'];?>  </td>   <td> <?php echo $row['ticket_id'];?>  </td> 
<td> <?php echo $row['time'];?>  </td>   
<td> <?php echo $row['transfer_to'];?>  </td>   <td> <?php echo $row['reason'];?>  </td> 
  <td> <?php echo $row['approved_by'];?>  </td>   


</tr>

</tbody>



<?php
}
?>

</table>

</span>

 

 

I should point out, i have a setinterval that calls the function every 5 seconds to show any updates in the table.

I also have a ajax function that checks every few seconds for a break...it's called breakRequest() as an onload in the body

[mcerveni]

also..when i press a button called Issue Tracker, it hangs as well. there isn't even any data in the table lol.

it's been getting worse and worse as i've been adding more functions to it.

 

 

[corbin]

You can't define a function inside of a function (well technically you can, but it's not suggested).

 

 

The first time the function is run, the variable will be empty hence no call back being executed.

 

 

As for the slowness....  The date string generating could probably be handled better (using DATE_SUB instead), but besides that, nothing seems inherently wrong SQL-wise.

 

 

Have you done an EXPLAIN on all of the queries that you're using to make sure indexes are probably being used?

[mcerveni]

Ok thanks,

 

and no i haven't done EXPLAIN. I actually never heard of it until now.

To be honest..i don't have indexes on every table. I never understood how dramatic the difference is with having an index.

 

For one of my tables called 'transfers' i don't have an index ...but i could apply it to my 'ticket_id' field, since there shouldnt be identical ticket numbers anyway.

 

[kickstart]

Hi

 

Would appear the main field you are searching on is the date field. Is that indexed?

 

However not sure if you simpified the code to post here but you appear to be searching for a date that is between 2 identical dates (why not use =).

 

Also might be worth thinking of a way to cache the returned data rather than retrieve it all the time. This could be worthwhile if loads of people are all hitting the server every 5 seconds to perform the same search. Could be worth only doing the search every second, with every other search within that second retrieving the cached version.

 

All the best

 

Keith