Slow query

[Andy-H]

I have this query which is taking 30 seconds to execute

 

 

            $query = "SELECT t.deviceid AS device, t.localname AS name, rfid.driver, 
                     nl.lat, nl.lon, nl.speed, nl.heading, nl.inputi AS ignition, nl.thetimestamp AS time
                     FROM trackers t
                     INNER JOIN ( newlocations nl ) ON ( t.unitid = nl.device )
                     LEFT JOIN  ( rfid )            ON ( nl.ex1   = rfid.tagID )
                     WHERE t.custid = ". $this->_user->companyID ." GROUP BY t.deviceid
                     ORDER BY nl.id, t.localname DESC";

 

 

newlocations has around 3 million rows, but I only want to return the latest row joined with the other data, is there any way to do so efficiently? Or will I just have to run 2-3 querys?

 

 

Cheers, Andy

[fenway]

Yes, there is a more efficient way -- first grab the latest row, then join back.

[Andy-H]

So select from newlocations and join trackers?

[fenway]

First, forget about the joins -- write the query that will find the most recent values that you want.

[Andy-H]

            $query = "SELECT t.deviceid AS device, t.unitid, t.localname AS name 
                     FROM trackers t
                     WHERE t.custid = ". $this->_user->companyID ." 
                     ORDER BY t.localname ASC";
            $result = mysql_query($query);
            if ( !mysql_num_rows($result) )
               throw new APIException('FAIL', 'No devices attached to your company');
            while ( $row = mysql_fetch_assoc($result) )
            {
               $query = "SELECT nl.lat, nl.lon, nl.speed, ROUND(nl.heading) AS heading, nl.inputi AS ignition, nl.thetimestamp AS time, nl.ex1
                        FROM newlocations nl 
                        WHERE nl.device = '". $row['unitid'] ."' 
                        ORDER BY nl.thetimestamp DESC LIMIT 1";
               unset($row['unitid']);
               $res = mysql_query($query);
               while ( $loc = mysql_fetch_assoc($res) )
               {
                  if ( !empty($loc['ex1']) )
                  {
                     $query         = "SELECT driver FROM rfid WHERE tagID = '". $loc['ex1'] ."' LIMIT 1";
                     $r             = mysql_query($query);
                     if ( mysql_num_rows($r) )
                        $loc['driver'] = mysql_result($r, 0, 0);
                  }

[fenway]

I see a lot of PHP code -- what I meant was produce an SQL query that will find the most recent one.  Then the join will be more obvious.