How do I control retrieving data from tables in a database

Question

I do have multiple tables in single database. when my page loads it retrieve data s from all tables in this database. because of this my page is getting slow due to different loading time in different tables. my question is how do I display page with data which is loaded already from tables and rest is in a streaming mode?? I don't know if it is possible with php and MySQL? any Ideas?

Thanks

Mathew

Answer 1

There is nothing bad in getting data from the different tables. Every site does that.
If some of your queries getting slow, you just have to find out which one and then optimize it.

You can find out slow queries with this simple code: just put these $TIMER statements before and after each query execution

<?
$TIMER['start']=microtime(TRUE);
// some code
$query="SELECT ...";
$TIMER['before q1']=microtime(TRUE);
  $res=mysql_query($query);
$TIMER['after q1']=microtime(TRUE); 
  while ($row = mysql_fetch_array($res)) {
// some code
  }
$TIMER['before q2']=microtime(TRUE);
  $res=mysql_query($query);
$TIMER['after q2']=microtime(TRUE); 
  while ($row = mysql_fetch_array($res)) {
// some code
  }
$TIMER['array filled']=microtime(TRUE); 
// some code
$TIMER['pagination']=microtime(TRUE); 

if ('127.0.0.1' === $_SERVER['REMOTE_ADDR']) { //put your IP addr here
  echo "<table border=1><tr><td>name</td><td>so far</td><td>delta</td><td>per cent</td></tr>";
  reset($TIMER);
  $start=$prev=current($TIMER);
  $total=end($TIMER)-$start;
  foreach($TIMER as $name => $value) {
    $sofar=round($value-$start,3);
    $delta=round($value-$prev,3);
    $percent=round($delta/$total*100);
    echo "<tr><td>$name</td><td>$sofar</td><td>$delta</td><td>$percent</td></tr>";
    $prev=$value;
  }
    echo "</table><>";
}
?>