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

pixelhero · 09-29-2011, 02:03 AM

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

)
????: NamePros.com http://www.namepros.com/programming/732363-mysql-query-taking-substantial-amount-time.html

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.

PHP Code:

  //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'];
????: NamePros.com http://www.namepros.com/showthread.php?t=732363
    $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

pixelhero · 09-29-2011, 02:36 PM

Have resolved this now.

Thanks

Peter · 09-29-2011, 03:55 PM

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

pixelhero · 09-30-2011, 03:21 AM

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:

PHP Code:

  
$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 
????: NamePros.com http://www.namepros.com/showthread.php?t=732363
????: NamePros.com http://www.namepros.com/showthread.php?t=732363
        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.

09-29-2011, 02:03 AM	THREAD STARTER #1 (permalink)
pixelhero NamePros Regular Join Date: May 2008 Location: England Posts: 771	MySQL Query taking substantial amount of time to run.. can you help? 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 ) ????: NamePros.com http://www.namepros.com/programming/732363-mysql-query-taking-substantial-amount-time.html 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. PHP Code: //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']; ????: NamePros.com http://www.namepros.com/showthread.php?t=732363 $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 __________________ pixelhero Web Development Studios UK Last edited by pixelhero; 09-29-2011 at 02:10 AM.

09-29-2011, 02:36 PM	THREAD STARTER #2 (permalink)
pixelhero NamePros Regular Join Date: May 2008 Location: England Posts: 771	Have resolved this now. Thanks __________________ pixelhero Web Development Studios UK

09-29-2011, 03:55 PM	#3 (permalink)
Peter NamePros Expert Join Date: Nov 2003 Location: Scotland Posts: 5,074	Out of interest how did you resolve it? presumably using indexes etc? __________________ Manage your portfolio using my new Domain Portfolio Management script. Securing Your Domain Name From Theft

09-30-2011, 03:21 AM	THREAD STARTER #4 (permalink)
pixelhero NamePros Regular Join Date: May 2008 Location: England Posts: 771	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: PHP Code: $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 ????: NamePros.com http://www.namepros.com/showthread.php?t=732363 ????: NamePros.com http://www.namepros.com/showthread.php?t=732363 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. __________________ pixelhero Web Development Studios UK Last edited by pixelhero; 09-30-2011 at 03:25 AM.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Welcome to the “I have mono so I’m home and have some extra time to run this auction”	RPK	.TV Marketplace	18	07-10-2011 10:07 AM
Great Scripts for Sale With Resale Rights!	Zeeble	Scripts For Sale	20	01-04-2006 01:39 AM
MySQL Tips	shahid_146	Webmaster Tutorials	4	10-18-2005 12:53 PM
Tutorial: How to Install Apache2 MySQL and PHP on Windows	deadserious	Webmaster Tutorials	35	09-21-2005 09:46 PM
Tutorial: Getting Started With MySQL (The Basics)	deadserious	Webmaster Tutorials	3	04-18-2004 01:17 PM

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)