MySQL Query taking substantial amount of time to run.. can you help?

[pixelbypixel]

Hi all,

I am converting some site from asp classic in to php, and the site requires a search of users based on distance. The old SQL ran pretty quickly, but did not contain as many fields. I have replicated it as close as I can, with a more normalized database (which seems like the reason it is so slow, never agreed with normalization )

My query will get all users data within X distance based on a postcode entry to search from. It will add limits for profile fields if the user selects any, but that is not causing the slow times.

Here is the SQL I have in my search functionality.

//Get the longitude and latitude of the postcode searched for.
$posSQL = "SELECT lat, `long` FROM cly_postcodes WHERE postcode = '$postcode'";
	
$rows = $db->pdo->prepare($posSQL);
foreach ($db->pdo->query($posSQL) as $row){
	$lat = $row['lat'];
	$lng = $row['long'];
}
	
//Select users within requested distance
$sql = "SELECT * FROM cly_profile_fields as k
	LEFT JOIN (SELECT id, `status` AS userStatus FROM cly_users AS u) AS uid ON k.userid = uid.id
	LEFT JOIN ( SELECT location, postcode, (3958.75586574 * ACOS(SIN($lat / 57.2957795130823) * SIN(lat / 57.2957795130823) + COS($lat / 57.2957795130823) * COS(lat / 57.2957795130823) * COS(`long` / 57.2957795130823 - $lng / 57.2957795130823)))  AS distance FROM cly_postcodes HAVING distance) AS t ON t.postcode = k.postcode";
					
//Must be a member or above.
$sql .= " WHERE userStatus >= 2";

// This line is a fix for where 'distance' was returning NULL - When I used 'AND distance IS NOT NULL' I was getting even longer delays in the time it took to run
$sql .= " AND k.postcode IN (SELECT postcode FROM cly_postcodes)";

//Get age values, ignore the fact these look backwards.
$sql .= " AND dob >= '$maxAge' AND dob <= '$minAge'";

//distance must be within specified distance.
$sql .= " AND t.distance <= $distance";

//order by distance
$sql .= " ORDER BY distance ASC";

//get first 12 results
$sql .= " LIMIT 0, 12";

Searching within 10 miles, is slow.
Searching within 100 miles, is going to lose my client customers, likely because they have died before it gives them any results.


My last attempt at this query checked for distance <= $distance in table t, and then I used distance IS NOT NULL at the end, just as bad if not worse.

Really, we are talking 15min searches, and MySQL using 25% cpu and locking up the site.

Using PDO connection.

Is this my script, or is this the server it is on do you think?

The 'old' classic asp script stored each users 'lat and long, location' in cly_profile_fields rather than accessing the cly_postcodes fields to get it, allowing calculations of the distance to be used in a WHERE statement, rather then creating temp fields and using HAVING, as well as a LEFT JOIN. - Would duplicating data like this improve the performance of the query a lot? It seems to run pretty instantly on the old site (same server). Does anyone know much about how heavy a LEFT JOIN on 3000 users, calculating the distance for each is, compared to just calculating distance for 3000 users being <= $distance?

Thanks for any help!

Richard,
PixelHero

 

[pixelbypixel]

Have resolved this now.

Thanks

 

[Peter]

Out of interest how did you resolve it? presumably using indexes etc?

 

[pixelbypixel]

The LEFT JOIN for the calculated distance was being very slow - even after applying indexes.

I have instead rewritten the site to store the lat and long of a user alongside their profile_fields data, so the query now looks like:

$posSQL = "SELECT lat, `long` FROM cly_postcodes WHERE postcode = '$postcode'";
	
$rows = $db->pdo->prepare($posSQL);
foreach ($db->pdo->query($posSQL) as $row){
	$lat = $row['lat'];
	$lng = $row['long'];
}
	
//Select users within requested distance
$sql = "SELECT *, (3958.75586574 * ACOS(SIN($lat / 57.2957795130823) * SIN(lat / 57.2957795130823) + COS($lat / 57.2957795130823) * COS(lat / 57.2957795130823) * COS(`long` / 57.2957795130823 - $lng / 57.2957795130823))) as distance 
        FROM cly_profile_fields as k 
        LEFT JOIN (SELECT id, `status` AS userStatus FROM cly_users AS u) AS uid ON k.userid = uid.id";
...
$sql .= " WHERE userStatus >= 2";
...
$sql .= " AND k.visible = 0";
...
$sql .= " AND k.postcode != ''";
...
$sql .= " AND dob >= '$maxAge' AND dob <= '$minAge'";
...
$sql .= " HAVING distance <= $distance";

One less left join. Works pretty instantly now rather than killing the server.