Postcode radius search very slow

[S3NTIN3L]

I think the reason this search takes a long time is that its using PHP (SQL instead is quicker?):

function calc_postcode_radius($distance, $postcode)
	{
		$postcode_count = 0;

	// Increment through all postcode IDs in DB
	for($i = 1; $i < 2821; $i++)
	{
		// Get position of users postcode
		$result_1 = mysql_query("SELECT Pcode, Grid_N, Grid_E FROM postcodes WHERE Pcode='$postcode' LIMIT 1");
		$row_1=mysql_fetch_array($result_1);
		$gridn[0]=$row_1['Grid_N'];
		$gride[0]=$row_1['Grid_E'];

		$result_2 = mysql_query("SELECT Pcode, Grid_N, Grid_E FROM postcodes WHERE Postcode_ID = $i LIMIT 1");
		$row_2=mysql_fetch_array($result_2);
		$gridn[1]=$row_2['Grid_N'];
		$gride[1]=$row_2['Grid_E'];		

		// TAKE GRID REFS FROM EACH OTHER.
		$distance_n=$gridn[0]-$gridn[1];
		$distance_e=$gride[0]-$gride[1];

		// CALCULATE THE DISTANCE BETWEEN THE TWO POINTS
		$hypot = sqrt(($distance_n*$distance_n)+($distance_e*$distance_e));
		$text = round($hypot/1000,2);

		if($text < $distance)
		{
			$postcode_count++;
		}
	}	
	return $postcode_count;
}

Database structure e.g:
Postcode_ID Pcode Grid_N Grid_E Latitude Longitude
1 AB10 392900 804900 57.135 -2.117

Someone please say how I could speed this up.

[bradgrafelman]

You could speed it up by doing exactly what you hinted at already - make the SQL server do all of the logic that you need in order to find the data you want, rather than just dumping everything and letting PHP sort through it.

Get rid of the second query (it was never needed anyway - just use a self join instead). Also add a WHERE condition that only SELECTs the rows which meet the applicable condition(s) (e.g. the distance between the two points is less than $distance).

[S3NTIN3L]

Well ive given that a try:

	function calc_postcode_radius($distance, $postcode)
	{
		$postcode_count_query = mysql_query("SELECT p1.Pcode, p1.Grid_N, p1.Grid_E, p2.Pcode, p2.Grid_N, p2.Grid_E
											 FROM postcodes p1
											 INNER JOIN postcodes p2
											 ON p1.Pcode = p2.Pcode
											 WHERE SQRT(((p1.Grid_N - p2.Grid_N)*(p1.Grid_N - p2.Grid_N))+((p1.Grid_E - p2.Grid_E)*(p1.Grid_E - p2.Grid_E))) < $distance");

	$result  = mysql_num_rows($postcode_count_query);

	return $result;
}

I get the WHERE clause you talk about (probably could be more elegant) but could you give me some hints on the inner join please.

Obviously need to include $postcode also not sure how though...

[johanafm]

Should that not rather be

ON SQRT(...) < $distance
WHERE p1.postcode = $postcode

[S3NTIN3L]

Yes that looks logical thanks.

Although I only ever get one result very quickly, this is what I got:

"SELECT p1.Pcode, p1.Grid_N, p1.Grid_E, p2.Pcode, p2.Grid_N, p2.Grid_E
FROM postcodes p1
INNER JOIN postcodes p2
ON SQRT(((p1.Grid_N - p2.Grid_N)*(p1.Grid_N - p2.Grid_N))+((p1.Grid_E - p2.Grid_E)*(p1.Grid_E - p2.Grid_E))) < $distance
WHERE p1.Pcode = '$postcode'"

[johanafm]

Ah well, to retain the functionality from your php code, it seems

round($hypot/1000,2);

is missing in your sql statement. I.e.

ON ROUND(SQRT(((p1.Grid_N - p2.Grid_N)*(p1.Grid_N - p2.Grid_N))+((p1.Grid_E - p2.Grid_E)*(p1.Grid_E - p2.Grid_E))) / 1000.2) < $distance

And furthermore, you might want to exclude the same postcode from being found (0 distance...)

ON ROUND(SQRT(((p1.Grid_N - p2.Grid_N)*(p1.Grid_N - p2.Grid_N))+((p1.Grid_E - p2.Grid_E)*(p1.Grid_E - p2.Grid_E))) / 1000.2) < $distance AND p1.postcode != p2.postcode

[S3NTIN3L]

Now the last question I will ask is how to then JOIN to the members table i.e. find all the postcodes that are in the given radius and are in the members table.
This is my attempt, is it right, cheers learnt a lot from this thread:

$postcode_count_query = mysql_query("SELECT p1.Pcode, p1.Grid_N, p1.Grid_E, p2.Pcode, p2.Grid_N, p2.Grid_E, m.postcode
FROM postcodes p1										 
INNER JOIN postcodes p2
ON ROUND(SQRT(((p1.Grid_N - p2.Grid_N)*(p1.Grid_N - p2.Grid_N))+((p1.Grid_E - p2.Grid_E)*(p1.Grid_E - p2.Grid_E))) / 1000.2) < $distance				 
JOIN members m 
ON p2.Pcode = m.postcode
WHERE p1.Pcode != p2.Pcode
AND p1.Pcode = '$postcode'");

[johanafm]

I'd write the query starting from the members table. While it doesn't actually matter which way you inner join things (it may matter with outer joins), I find it easier to start with what I have: one member and go from there towards what I want.

SELECT ...
FROM member m
-- you want the coordinates for the members postcode
INNER JOIN postcode p ON m.postcode = p.postcode
-- and then all postcodes inside a given radius from the first coordinate
INNER JOIN postcode in_radius ON (calculate_distance()) < @distance
-- for one specific member
WHERE m.id = @member_id

Furthermore, I'd also ditch the p.postcode != in_radius.postcode and instead not select anything from p, but only from in_radius. That way, you don't have the members postcode and coordinates on every single row of the table like this

members_postcode   members_gridN    members_gridE    in_radius.postcode   ...

So, also modify the select part of your query

SELECT in_radius.postcode, in_radius.gridN, in_radius.gridE

[S3NTIN3L]

Well I am half there I think - im getting all postcodes in the radius selected, but not all postcodes in the radius selected that are in the members table.

"SELECT p.Pcode, p.Grid_N, p.Grid_E, in_radius.Pcode, in_radius.Grid_N, in_radius.Grid_E, m.postcode
FROM members m									 
INNER JOIN postcodes p 
ON m.postcode = p.Pcode
INNER JOIN postcodes in_radius 
ON ROUND(SQRT(((p.Grid_N - in_radius.Grid_N)*(p.Grid_N - in_radius.Grid_N))+((p.Grid_E - in_radius.Grid_E)*(p.Grid_E - in_radius.Grid_E))) / 1000.2) < $distance
WHERE m.member_id = $member_id"

[johanafm]

Well, you obviously have to join in the members table then

-- the member performing the search
FROM members m
-- that member's postcode					 
INNER JOIN postcodes p ON m.postcode = p.Pcode
-- all postcodes within that radius
INNER JOIN postcodes in_radius 
ON ROUND(SQRT(((p.Grid_N - in_radius.Grid_N)*(p.Grid_N - in_radius.Grid_N))+((p.Grid_E - in_radius.Grid_E)*(p.Grid_E - in_radius.Grid_E))) / 1000.2) < $distance
-- members having these postcodes
INNER JOIN ...

WHERE m.member_id = $member_id"