title: mysql database running very very slow
body: <p>I have written a web application, (my first one) which is now running very very slow some of the ajax '$post's are taking up to 40 seconds.</p>
  
  <p>I cannot supply the entire code for the site as it would take up too much space. I need to re factor it so that is runs faster.</p>
  
  <p>when the website loads it does around 7 calls 7 scripts at the same time, all of which are retrieving data from a mysql database. </p>
  
  <p>Q1 can you create a database queue in php (like fmdatabase.queue in obj-c)? so that there is one connection to the db and the call happen one after the other</p>
  
  <p>the database has around 1 million points in it (which I was told should not lead to performance issues). I am using mysql not mysqli from the reading that I have done I wont see an improvement in speed from changing to mysqli. Is this right? </p>
  
  <p>here is my database creating script:</p>
  
  <pre><code>$TripsData = "
      CREATE TABLE IF NOT EXISTS trips_data (
          id INT(10) NOT NULL AUTO_INCREMENT,
          userid INT(10),
          tripid INT,
          gmttimestamp BIGINT,
          latitude DOUBLE(24,20),
          longitude DOUBLE(24,20),
          altitude DOUBLE(24,20),
          accuracy DOUBLE(24,20),
          speed DOUBLE(24,20),
          species INT,
          killcount INT,
          PRIMARY KEY (id)
      ) TYPE =MYISAM
  ";
  </code></pre>
  
  <p>and here is one of my scripts for getting user data</p>
  
  <pre><code>&lt;?php
  include ('header.php');
  $userid=isloggedin();
  if(isset($_POST['tripid'])){
  $tripid=$_POST['tripid'];
  $tripidbool=true;
  }else{
    $tripidbool=false;
  }
    if(isset($_POST['gamebook'])){$gamebookbool=true;}else{$gamebookbool=false;}
    if(isset($_POST['bounds'])){$bounds=$_POST['bounds'];$boundsboo=true;         }else{$boundsboo=false;}
  
  if($tripidbool&amp;&amp;!$boundsboo){
  
      $totaldistanceinm=getTotalDistanceInM($userid, $tripid);        
  echo '&lt;tr&gt;&lt;td&gt; Distance Traveled &lt;/td&gt;&lt;td&gt; '.number_format(($totaldistanceinm/1000),2).' Km ('.number_format(($totaldistanceinm/1000)*(0.62137),2).' miles)&lt;/td&gt;&lt;/tr&gt;';
  
       $query="SELECT MIN(gmttimestamp),
           MAX(gmttimestamp),
           AVG(speed), 
           SUM(killcount)
          from trips_data WHERE tripid=$tripid AND userid=$userid";   
       $result=mysql_query($query)or die(' '.mysql_error());
       $row=mysql_fetch_array($result);
  
  
  echo'&lt;tr&gt;&lt;td&gt;Start Time &lt;/td&gt;&lt;td&gt; '. date('H:i',$row[0]).'&lt;/td&gt;&lt;/tr&gt;'; 
  
  echo '&lt;tr&gt;&lt;td&gt;Finish Time &lt;/td&gt;&lt;td&gt; '. date('H:i',$row[1]).'&lt;/td&gt;&lt;/tr&gt;';
  
      $dur=  $row[1]-$row[0];
      if($dur!==0)
      {
          $hrs=($dur-($dur%3600))/3600;
          $mins=((($dur%3600)-($dur%60))/60);
          echo '&lt;tr&gt;&lt;td&gt;Duration&lt;/td&gt;&lt;td&gt;'.$hrs.' Hours '.$mins.' Mins &lt;/td&gt;&lt;/tr&gt; ';
      }
  
  $totalk= $row[3];
  echo '&lt;tr&gt;&lt;td&gt;Total Kills&lt;/td&gt;&lt;td&gt;'.$totalk .'&lt;/td&gt;&lt;/tr&gt;';
  if($dur!==0)
      {
          echo '&lt;tr&gt;&lt;td&gt;Kills/Hour&lt;/td&gt;&lt;td&gt;'.number_format(($totalk*3600/$dur),2).'&lt;/td&gt;&lt;/tr&gt;';
      }
  
  $total=$row[2];
  echo '&lt;tr&gt;&lt;td&gt;Avg Speed m/s&lt;/td&gt;&lt;td&gt;'.number_format($total,2).'&lt;/td&gt;&lt;/tr&gt;';
  
      $query="SELECT SUM(gmttimestamp) FROM trips_data WHERE userid=$userid AND tripid=$tripid AND speed&gt;0";
  $res=mysql_query($query) or die(' '.mysql_error());
  $row=mysql_fetch_array($res);
      $timeMoving=$row[0];
  
      $query="SELECT SUM(gmttimestamp) FROM trips_data WHERE userid=$userid AND tripid=$tripid AND speed=0";
  $res=mysql_query($query) or die(' '.mysql_error());
  $row=mysql_fetch_array($res);
      $timeNotMoving=$row[0];
  
      $toatltime=$timeMoving+$timeNotMoving;
  
      echo '&lt;tr&gt;&lt;td&gt;%Time Moving/Stationary&lt;/td&gt;&lt;td&gt;'.number_format(($timeMoving/$toatltime)*100,2).'/'.number_format(($timeNotMoving/$toatltime)*100,2).'&lt;/td&gt;&lt;/tr&gt;';
     // SELECT sum(trips_data.killcount), trips_data.species,masterspecies.species from trips_data join masterspecies WHERE tripid=335 AND userid=1 AND NOT killcount=0
  
  $query="SELECT sum(trips_data.killcount) as kills,
          trips_data.species as species_id,
          masterspecies.species
              FROM trips_data
              JOIN masterspecies ON masterspecies.speciesidno = trips_data.species
              WHERE tripid =$tripid
              AND userid =$userid
              AND NOT killcount =0
              GROUP BY trips_data.species
              LIMIT 0 , 30";
  $res=mysql_query($query)or die('87'.mysql_error());
  
  while ($row=mysql_fetch_assoc($res))
      {
      $species=$row['species_id'];
      $SpeciesName="";
  
      if($species&lt;0){
          $species*=-1;
          $SpeciesName="Seen ";
  
      }
      $SpeciesName.=$row['species'];
      $totalkills=$row['kills'];
  
      echo '&lt;tr&gt;&lt;td&gt;Total '.$SpeciesName.'s&lt;/td&gt;&lt;td&gt; '.$totalkills.' &lt;/td&gt;&lt;/tr&gt;';
  }
  
   }
   elseif ($boundsboo&amp;&amp;!$tripidbool){
      $minlat=$_POST['minlat'];
      $maxlat=$_POST['maxlat'];
      $minlong=$_POST['minlon'];
      $maxlong=$_POST['maxlon'];
  
      $latlon=getCenterPointForBox($minlat, $minlong, $maxlat, $maxlong);
      $farmerId=  getFarmerIdForCenterPoint($latlon[0], $latlon[1]);
  
      if($farmerId)
      {
          $where=getWhereClauseForFarmersBoundaries(getBoundaryBoxesForFarmer($farmerId));
          $query="SELECT DISTINCT tripid FROM trips_data WHERE (".$where .") AND userid=$userid order by gmttimestamp desc";
  
      }  else {
         $query="SELECT DISTINCT tripid FROM trips_data WHERE userid=$userid 
          AND latitude BETWEEN $minlat AND $maxlat 
          AND longitude BETWEEN $minlong AND $maxlong "; 
      }
  
  
  $res=mysql_query($query) or die(' '.$query.' '.mysql_error());
  $totaldistanceinm=0;
      $number=mysql_num_rows($res);
  
      $dur=0;
  for($a=0;$a&lt;$number;$a++){
      $row=mysql_fetch_array($res);
      $ctripid=$row[0];
  
      $longprev=0;
      $latprev=0;
      $longdegstometers=10000000/90;
      $latdestom=0;
  
  
      $query_1="SELECT gmttimestamp,latitude, longitude FROM trips_data WHERE tripid=$ctripid AND userid=$userid ORDER BY gmttimestamp";
      $resa=mysql_query($query_1)or die(' '.mysql_error().$query_1);
      for($i=0;$i&lt;mysql_num_rows($resa);$i++){
          $rowa=mysql_fetch_assoc($resa);
          $long=$rowa['longitude'];
          $lat=$rowa['latitude'];
          if($i==0){
                              $st=$rowa['gmttimestamp'];
              $longprev=$long;
              $latprev=$lat;
              $latdestom=cos($long)*$longdegstometers;
          }
          if($i&gt;=1){
              $dlongM=($longprev-$long)*$longdegstometers;
              $dlatM=($latprev-$lat)*$latdestom;
              $distance=sqrt(($dlatM*$dlatM)+($dlongM*$dlongM));
              $totaldistanceinm+=$distance;
              $longprev=$long;
              $latprev=$lat;
                              $et=$rowa['gmttimestamp'];
          }
  
      }
              $dur+=($et-$st);
  
  
      }
  
  echo '&lt;tr&gt;&lt;td&gt;Total Distance Traveled &lt;/td&gt;&lt;td&gt; '.number_format(($totaldistanceinm/1000),2).
  ' Km ('.number_format(($totaldistanceinm/1000)*(0.62137),2).' miles)&lt;/td&gt;&lt;/tr&gt;';
  $hrs=($dur-($dur%3600))/3600;
  $mins=((($dur%3600)-($dur%60))/60);
  echo '&lt;tr&gt;&lt;td&gt;Total Contact&lt;/td&gt;&lt;td&gt;'.$hrs.' Hours '.$mins.' Mins &lt;/td&gt;&lt;/tr&gt; ';
  echo '&lt;tr&gt;&lt;td&gt;Total Number of Trips&lt;/td&gt;&lt;td&gt;'.$number.'  &lt;/td&gt;&lt;/tr&gt; ';
  
  if(!$where)$where=" latitude BETWEEN $minlat AND $maxlat 
          AND longitude BETWEEN $minlong AND $maxlong ";
  $query="SELECT DISTINCT species FROM trips_data WHERE userid=$userid AND killcount&gt;0 AND ".$where ." ";
  $re_sp=  mysql_query($query) or die(''.  mysql_error());
      for($i=0;$i&lt;  mysql_num_rows($re_sp);$i++)
      {
  
      $row=  mysql_fetch_array($re_sp);
      $species=$row[0];
              if($species==0){continue;}
      $SpeciesName="";
  
              $iiquery="SELECT SUM(killcount) FROM trips_data WHERE userid=$userid
        AND species=$species AND (".$where.") ";
  
      $iires=mysql_query($iiquery) or die('196 '.mysql_error());
      $iirow=mysql_fetch_array($iires);
      $totalkills=$iirow[0];
  
      if($species&lt;0){
          $species*=-1;
          $SpeciesName="Seen ";
  
      }
  
      $iquery="SELECT species FROM masterspecies WHERE speciesidno=$species";
      $ires=mysql_query($iquery) or die('191 '.mysql_error());
      $irow=mysql_fetch_array($ires);
      $SpeciesName.=$irow[0];
  
  
  
      echo '&lt;tr&gt;&lt;td&gt;Total '.$SpeciesName.'s&lt;/td&gt;&lt;td&gt; '.$totalkills.' &lt;/td&gt;&lt;/tr&gt;';
  }
  
  }
  
  
  if($gamebookbool){
  $longprev=0;
  $latprev=0;
  $longdegstometers=10000000/90;
  $latdestom=0;
  $totaldistanceinm=0;
  
  $query="SELECT MIN(gmttimestamp) from trips_data WHERE userid=$userid";
  $result=mysql_query($query)or die(' '.mysql_error());
  $row=mysql_fetch_array($result);
  $st=$row[0];
  $date=date("j:F",$st);
  echo'&lt;tr&gt;&lt;td&gt; First Trip &lt;/td&gt;&lt;td&gt; '.$date.'&lt;/td&gt;&lt;/tr&gt;';
  
    //for all trips
      $allTripsForHunter=allTripsForHunter($userid);
      $yearDistance=0;
      for($i=0;$i&lt;count($allTripsForHunter);$i++){
  $query="SELECT latitude, longitude FROM trips_data WHERE tripid=$allTripsForHunter[$i] AND userid=$userid ORDER BY gmttimestamp";
  
   $res=mysql_query($query)or die(' '.mysql_error());
  i f(mysql_num_rows($res)&gt;0){    
      for($i=0;$i&lt;mysql_num_rows($res);$i++){
       $row=mysql_fetch_assoc($res);
      $long=$row['longitude'];
      $lat=$row['latitude'];
      if($i==0){
          $longprev=$long;
          $latprev=$lat;
          $latdestom=cos($long)*$longdegstometers;
      }
      if($i&gt;=1){
          $dlongM=($longprev-$long)*$longdegstometers;
          $dlatM=($latprev-$lat)*$latdestom;
          $distance=sqrt(($dlatM*$dlatM)+($dlongM*$dlongM));
          $totaldistanceinm+=$distance;
          $longprev=$long;
          $latprev=$lat;
          }
      }
              $yearDistance+=$totaldistanceinm;
          }
      }
  echo '&lt;tr&gt;&lt;td&gt; Distance Traveled &lt;/td&gt;&lt;td&gt; '.number_format(($yearDistance/1000),2).' Km ('.number_format(($yearDistance/1000)*(0.62137),2).' miles)&lt;/td&gt;&lt;/tr&gt;';
  
  
      $yearDuration=0;
  
      for($i=0;$i&lt;count($allTripsForHunter);$i++){
          $query="SELECT MIN(gmttimestamp) from trips_data WHERE tripid=$allTripsForHunter[$i] AND userid=$userid";   
          $result=mysql_query($query)or die(' '.mysql_error());
          $row=mysql_fetch_array($result);
          $st=$row[0];
          $starttime=date('H:i',$row[0]);
  
          $query="SELECT MAX(gmttimestamp) from trips_data WHERE tripid=$allTripsForHunter[$i] AND userid=$userid";
          $result=mysql_query($query)or die(' '.mysql_error());
          $row=mysql_fetch_array($result);
          $et=$row[0];
          $starttime=date('H:i',$row[0]); 
  
          $dur=($et-$st);
          $yearDuration+=$dur;
      }
      $days=($yearDuration-($yearDuration%86400))/86400;
      $hrs=(($yearDuration%86400)-($yearDuration%3600))/3600;
  $mins=((($yearDuration%3600)-($yearDuration%60))/60);
  
      echo '&lt;tr&gt;&lt;td&gt;Duration&lt;/td&gt;&lt;td&gt;'.$days.' Days '.$hrs.' Hours '.$mins.' Mins &lt;/td&gt;&lt;/tr&gt; '; 
  
      $query="SELECT SUM(killcount) FROM trips_data WHERE userid=$userid";
  $res=mysql_query($query) or die(' '.mysql_error());
  $row=mysql_fetch_array($res);
  $totalk=$row[0];
  echo '&lt;tr&gt;&lt;td&gt;Total Kills&lt;/td&gt;&lt;td&gt;'.$totalk.'&lt;/td&gt;&lt;/tr&gt;';
  echo '&lt;tr&gt;&lt;td&gt;Kills/Hour&lt;/td&gt;&lt;td&gt;'.number_format(($totalk*3600/$yearDuration),2).'&lt;/td&gt;&lt;/tr&gt;';
  
  $query="SELECT DISTINCT species from trips_data WHERE userid=$userid AND NOT killcount=0";
  $res=mysql_query($query)or die('87'.mysql_error());
  $number=mysql_num_rows($res);
  
  for($i=0;$i&lt;$number;$i++){
      $row=mysql_fetch_assoc($res);
  
      $species=$row['species'];
      $SpeciesName="";
  
              $iiquery="SELECT SUM(killcount) FROM trips_data WHERE userid=$userid AND species=$species";
      $iires=mysql_query($iiquery) or die('107 '.mysql_error());
      $iirow=mysql_fetch_array($iires);
      $totalkills=$iirow[0];
  
              if($species&lt;1){
          $species*=-1;
          $SpeciesName="Seen ";
  
      }
  
      $iquery="SELECT species FROM masterspecies WHERE speciesidno=$species";
      $ires=mysql_query($iquery) or die(' '.mysql_error());
      $irow=mysql_fetch_array($ires);
      $SpeciesName.=$irow[0];
  
  
  
      echo '&lt;tr&gt;&lt;td&gt;Total '.$SpeciesName.'s&lt;/td&gt;&lt;td&gt; '.$totalkills.' &lt;/td&gt;&lt;/tr&gt;';
  }
  
  
  
  }
  ?&gt;
  </code></pre>