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.

• rickyduck: Ok thanks, changed it to a join now. I tested by doing the whole of kettering and wellingborough, results are: load: 98796ms - parse: 0ms - display: 1ms, and the memory size gets exhausted. I could up memory time, but I'd rather segment
• rickyduck: Although it's not technically an answer, I will mark yours as correct as noone else has put any, and I have made my code 100x quicker as well.

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.