Nested while loop problem very slow in query

Databases
1

Hi I need some help please. I have records 280,000 or more in table “locations”, I am having trouble with my nested while loop,It takes more than 3 minutes to query the record to my database.and my ajax request is still showing loading icon in the firebug.in Net tab.

I know using native mysql is not good because it will be removed in the future ,but it takes time for me to recode the whole project ,I need to get this fix first before I will migrate to PDO.

Thank you in advance.

Here is the Php code.

position.php

<?php
include_once 'includes/dbconfig.php';


session_start();
$today = date('Y-m-d H:i:s');

$loginid = $_SESSION['loginid'];




$query_ = mysql_query("Select * from user_devices where user_id = '$loginid'")or die("problem in query = ".mysql_error());
$numrow_ = mysql_num_rows($query_);

if ($numrow_ > 0) {

    while ($row_ = mysql_fetch_array($query_,MYSQL_BOTH)) {

        $query = mysql_query("select ud.*,dt.*,dl.device_id as 'deviceid' ,dl.*,rfinfo.*
                                   from user_devices ud left join mst_device_types dt
                                    on ud.device_type = dt.id
                                   inner join locations dl
                                    on ud.device_id = dl.device_id
                                    left outer join rfidinfo rfinfo
                                    on  dl.device_id =rfinfo.device_id
                                   where dl.device_id = '$row_[2]' AND ud.user_id='$loginid'
                                    order by dl.id desc LIMIT 1
                                  ")or die("problem in query = ".mysql_error());


        while($row=mysql_fetch_array($query,MYSQL_BOTH)){
            $coord[]= trim($row['latitude']).','.trim($row['longitude']);
            .....
            ....
            ....
            ...
            ..
            ..
        }

    }

}



$return_data=array('coord'=>$coord,
    ....
    ....
    ...
    ...
    ...
    ...
    ...
    ....
);

echo json_encode($return_data);

?>

Here is my jquery.ajax that calls my Php script every 1 sec.

   $(function(){
      
       myinterval = setInterval(function(){
       $.ajax({
           type: "get",
           dataType:'json',
           url: "position.php",
           success: function(data){
                  //perform some operation
               }


           }

       });

         },1000);

   });
2

Why do you have two loops? Why not just use the internal query and just run it once? You would need to correct the join to use the appropriate table field instead of the $row[2], and perhaps use a sub-query instead of the joins since you’re limiting the internal query to one row.

At the very least, I would change the two queries to select only the fields that are needed. SELECT * queries can be very expensive depending on the size of the tables and slow you down significantly.

3

It is hard to know where the problem is but I can tell you something that will improve it (don’t know if much though)

$query = mysql_query("select ud.*,dt.*,dl.device_id as 'deviceid' ,dl.*,rfinfo.*
                                   from user_devices ud left join mst_device_types dt
                                    on ud.device_type = dt.id
                                   inner join locations dl
                                    on ud.device_id = dl.device_id
                                    left outer join rfidinfo rfinfo
                                    on  dl.device_id =rfinfo.device_id
                                   where dl.device_id = '$row_[2]' AND ud.user_id='$loginid'
                                    order by dl.id desc LIMIT 1
                                  ")or die("problem in query = ".mysql_error());

First, intead of using the old mysql_ try to use the improved myslqi

Secondly, don’t use the astherics (*) to add all the fields. I know that writing it is a pain but it is quicker for the database if you name each field that you want to show instead that using the astheris.

Example:

If a table has an id field, a name field and phone_number field, this query SELECT table.id, table.name, table.phone_number FROM table will execute faster than SELECT table.* FROM table

Of course, in such a small table you will probably not notice the difference unless you have a few millions of records but maybe in your database you’ll notice some difference

4

Thank you for your suggestion I will a give a try.I will change my code

5

I will try to fix this.thank you for your suggestion.

6

I’ll be back and I will let you know.

7

@DaveMaxwell,

I forgot to ask, you mean to change all my joins into subquery ?

Thank you in advance

8

You really should not specify the MYSQL_BOTH flag, since all you’re doing is fetching the data twice - once in an associative array and once in an indexed array. It’s a waste of resources.

You also should not need the nested WHILE loop since your $query query is limited to one result.

9

@DaveMaxwell,@tpunt,@molona,

This is my updated code,I don’t know yet how to use the sub-query in order to achieve my wants.,the problem of my code now is that only one $user_deviceid will be search in location table.

This is what I am trying to achieve in my query,.In user_devices if I have all of these kind of device_id

device_id
000001,
000004,
000005,
000008

I am going to search these in “location” table having the same with these device_id and if it found I will get the last record of each of these 4 device_id…but I only get the last record of the device_id “000001”.I could not get the other.

Thank you in advance.

$query_ = mysql_query("Select device_id from user_devices where user_id = '$loginid'")or die("problem in query = ".mysql_error());
$numrow_ = mysql_num_rows($query_);
 $userrow  = mysql_fetch_assoc($query_);
$user_deviceid = $userrow['device_id'];

if ($numrow_ > 0) {



        $queryloc = mysql_query("select dl.device_id,dl.latitude,dl.longitude,dl.dateTime_created,
                                 
                                   from user_devices ud left join mst_device_types dt
                                    on ud.device_type = dt.id
                                   inner join locations dl
                                    on ud.device_id = dl.device_id
                                    left outer join rfidinfo rfinfo
                                    on  dl.device_id =rfinfo.device_id
                                   where dl.device_id = '$user_deviceid' AND ud.user_id='$loginid'
                                    order by dl.id desc LIMIT 1
                                  ")or die("problem in query = ".mysql_error());


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

        }



}
10

What’s the structure of the tables concerned?

What’s the final output needed (fields required)

11

@SpacePhoenix , @r937

What’s the structure of the tables concerned?

locations table

CREATE TABLE `locations` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `device_id` varchar(20) NOT NULL,
  `latitude` varchar(15) NOT NULL,
  `longitude` varchar(15) NOT NULL,
  `dateTime_created` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `dateTime_created` (`dateTime_created`),
  KEY `device_id` (`device_id`)
) ENGINE=InnoDB AUTO_INCREMENT=241597 DEFAULT CHARSET=latin1

user_devices tables

CREATE TABLE `user_devices` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `device_id` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `device_id` (`device_id`)
) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=latin1

What’s the final output needed (fields required)

from locations table

device_id,latitiude,longitude,dateTime_created

$coord[]= trim($row['latitude']).','.trim($row['longitude']);
$devices[] = $row['device_id'];

$date = $row[‘dateTime_created’];

the return should like this in json

{ “coordinate”:[“123.8998,45.345656”,“123.34567,45.24567”,“123.34567,45.24565”,“123.34567,45.24569”],
“devices”:[“000001 “,” 000004”,“000005”,“000008”],
“date”:[“2014-09-01”,“2014-09-01”,“2014-09-01”,“2014-09-01”]

Thank you in advance.

12

Is that can be achieve without using some loop ?

13

Can you please post some sample data for each table? Thanks

15

sorry, i don’t do php

16

@DaveMaxwell,

Why not just use the internal query and just run it once?

I could not get the correct data if I will not use the while loop,if I have 1 while loop I can get the correct data.Is this what you mean ?

$query_ = mysql_query("Select device_id from user_devices where user_id = '$loginid'")or die("problem in query = ".mysql_error());
$numrow_ = mysql_num_rows($query_);

if ($numrow_ > 0) {

 while( $userrow  = mysql_fetch_assoc($query_)){
$user_deviceid = $userrow['device_id'];



        $queryloc = mysql_query("select dl.device_id,dl.latitude,dl.longitude,dl.dateTime_created,

                                   from user_devices ud left join mst_device_types dt
                                    on ud.device_type = dt.id
                                   inner join locations dl
                                    on ud.device_id = dl.device_id
                                    left outer join rfidinfo rfinfo
                                    on  dl.device_id =rfinfo.device_id
                                   where dl.device_id = '$user_deviceid' AND ud.user_id='$loginid'
                                    order by dl.id desc LIMIT 1
                                  ")or die("problem in query = ".mysql_error());


//I remove the while loop here.
            $row=mysql_fetch_assoc($queryloc);
            $coord[]= trim($row['latitude']).','.trim($row['longitude']);
            ......
            .....
            .....
            .....




 }

}
17

I remember doing some benchmarks some time ago and with mysql there was absolutely no speed difference between using an asterisk and listing all columns, even in a large loop.

Explicit fieldnames vs. asterisk wildcard in MySQL
18

@molona meant that you should not use the asterisk wild card if you do not need every column to be returned from the table (and in the OPs code, he doesn’t need all columns returned). So of course there should be no speed difference between listing all columns to be returned VS using the shorthand notation of the asterisk.

19

That’s fine if you can’t get the data (though I still think you probably could), you’re still doing more queries that you don’t need to do.

You can still re-arrange your queries to make them more efficient.

You can/should add the device_types table to the first 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'

Then change the internal query to only reflect the fields, and remove you actually need

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
21

@DaveMaxwell ,

Is there No while loop here ?

22

I just replaced the two queries in your original post with these two - they should be more efficient (you’ll notice the 2nd doesn’t include user_device since you’ve already selected it in the parent).

I just did the SQL to allow for readability. I thought it would be obvious it was just the SQL, but guess not.