Nested while loop problem very slow in query

Databases
23

@DaveMaxwell,

Thank you for the reply.

so the structure in my code is something like this

   $row1 = mysql_query(" SELECT u.device_id
         , dt.fieldname1
         , dt.fieldname2
      FROM user_devices u 
      JOIN mst_device_types dt ON ud.device_type = dt.id 
     WHERE user_id = '$loginid' ");

   

   while($row2 = mysql_fetch_assoc($row1)){
       $user_deviceid = $row2['device_id'];

       $query =  (" SELECT dl.device_id
     , dl.latitude
     , dl.longitude
     , dl.dateTime_created
     , other fields.....
  FROM locations dl
  LEFT OUTER JOIN rfidinfo rfinfo ON dl.device_id = rfinfo.device_id
 WHERE dl.device_id = '$user_deviceid' 
 ORDER BY dl.id DESC 
 LIMIT 1
 ");

    $row=mysql_fetch_assoc($query);
       $coord[]= trim($row['latitude']).','.trim($row['longitude']);
      .....
     ....
     ....



 }

Thank you in advance.

24

Should work assuming all the fields are there…

25

I moved a post to an existing topic: Explicit fieldnames vs. asterisk wildcard in MySQL