Make two MySQL statements & loop quicker

Question

Would a join be quicker here? I tested a join and it's almost the same time! Basically its a ray casting mechanism that finds all properties in a square on GMaps, and then there's a javascript function that finds the point in polygon. So at any time, the records fetched could be in the tens of thousands.

The first statement finds all the postcodes in the latLongs, the second one finds all the properties in that postcode.

Bear in mind I just knocked this up very quickly, as the raycasting javascript took up most of my designated time

    $latlongs = explode("), ", $_GET['bounds']);


$first = ($latlongs[0]);
$first = substr($first, 2);


$second = $latlongs[1];
$second = substr(substr($second, 1),0, -2);

$first = explode(", ", $first);
$second = explode(", ", $second);
//$result = mysql_query("SELECT * FROM `postcodeGoogle` WHERE lat");
$lat1 = $first[0];
$lat2 = $second[0];

$long1 = $first[1];
$long2 = $second[1];
$resultE = "";
$result = mysql_query("SELECT * FROM `postcode`.`postcodeGoogle` WHERE (`lat` BETWEEN '$lat1' AND '$lat2') AND (`long` BETWEEN '$long1' AND '$long2')")or die(mysql_error());

while($row = mysql_fetch_assoc($result)){


        $prop = mysql_query("SELECT * FROM `digital_hub`.`address_list` WHERE `postcode`='".$row['postcode']."'")or die(mysql_error());
        echo $row['postcode'];
        echo mysql_num_rows($prop);
        while($propRow=mysql_fetch_assoc($prop)){
            $resultE = $resultE."(".$row['lat'].", ".$row['long'].") || ";
        }
}
$resultE = substr($resultE, 0, -4);
echo $resultE;

Answer 1

Personally I would go with a join even if they are currently the same speed.

This way your code automatically gets upgraded when the DB gets upgraded (ie when a new index is added to the DB or they decide to partition the DB by latitude/longitude). In this situation if you are using a Join your code will automatically benefit from any improvement.

Answer 2

<?php 

include("db.php");
mysql_select_db("digital_hub");
$latlongs = explode("), ", $_GET['bounds']);


$first = ($latlongs[0]);
$first = substr($first, 2);


$second = $latlongs[1];
$second = substr(substr($second, 1),0, -2);

$first = explode(", ", $first);
$second = explode(", ", $second);
//$result = mysql_query("SELECT * FROM `postcodeGoogle` WHERE lat");
$lat1 = $first[0];
$lat2 = $second[0];

$long1 = $first[1];
$long2 = $second[1];
$resultE = "";
$result = mysql_query("SELECT * FROM `digital_hub`.`address_list` INNER JOIN `postcode`.`postcodeGoogle` ON `digital_hub`.`address_list`.`postcode` = `postcode`.`postcodeGoogle`.`postcode` WHERE `postcode`.`postcodeGoogle`.`lat` >= '$lat1' AND `postcode`.`postcodeGoogle`.`lat` <= '$lat2' AND `postcode`.`postcodeGoogle`.`long` >= '$long1' AND `postcode`.`postcodeGoogle`.`long` <= '$long2'")or die(mysql_error());

while($row = mysql_fetch_assoc($result)){



            $resultE = $resultE."(".$row['lat'].", ".$row['long'].") || ";

}
$resultE = substr($resultE, 0, -4);
echo $resultE;
?>

I then indexed all columns that were used by this query, and upped memory limit to 512M. Works a treat.