dcsimg
www.webdeveloper.com
Results 1 to 5 of 5

Thread: Slow Query Performace

  1. #1
    Join Date
    Jan 2009
    Posts
    27

    Slow Query Performace

    Hi All -

    I am using google maps to be displayed on my page using PHP...I am using the below query to retrieve some data...I see that it takes around 23 seconds to run in mysql query browser..Can anyone suggest me on how to improve this..

    $get_lat_long = "SELECT latitude, longitude FROM zip_codes WHERE Zip= {$_POST['hdn_src_zipcode']} LIMIT 1";

    $get_lat_long_res = mysql_query($get_lat_long);
    $fetch_lat_long = @mysql_fetch_assoc($get_lat_long_res);

    $v_lat = $fetch_lat_long['latitude'];
    $v_lon = $fetch_lat_long['longitude'];

    $query1 = "SELECT i.instructor_id, i.first_name, i.last_name, i.footage, ";
    $query1 .= "i.City, i.State, i.Zip, z.latitude, z.longitude, ";
    $query1 .= "(3959 * acos(cos(radians($v_lat) ) * cos(radians(latitude) ) * cos( radians( longitude ) - radians($v_lon)) + sin(radians($v_lat) ) * sin(radians(latitude)))) AS distance";
    $query1 .= " FROM instructors i, zip_codes z";
    $query1 .= " WHERE i.Zip = z.Zip";
    $query1 .= " HAVING distance < 40 ORDER BY distance";

  2. #2
    Join Date
    Aug 2009
    Posts
    84
    Hi, I would check if "Zip" column is correctly indexed.

  3. #3
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    2,120
    In addition to ensuring that all "search columns" are properly indexed, the following query will always be slow:

    $query1 = "SELECT i.instructor_id, i.first_name, i.last_name, i.footage, ";
    $query1 .= "i.City, i.State, i.Zip, z.latitude, z.longitude, ";
    $query1 .= "(3959 * acos(cos(radians($v_lat) ) * cos(radians(latitude) ) * cos( radians( longitude ) - radians($v_lon)) + sin(radians($v_lat) ) * sin(radians(latitude)))) AS distance";
    $query1 .= " FROM instructors i, zip_codes z";
    $query1 .= " WHERE i.Zip = z.Zip";
    $query1 .= " HAVING distance < 40 ORDER BY distance";
    It requires a full table-scan as-is. Even worse, it must derive a value for each row returned from the join. A DBMS can't really optimize for this type of operation.

    You may need to create a base "fuzzy" query that returns something similar to what you want, ordered the way you want, and then trim away the "fat." So, maybe you could select everything in the square that encompasses the circle, and then add the conditions to drop rows the fall outside of circle.

    Know what I mean?

  4. #4
    Join Date
    Jan 2009
    Posts
    27
    Thanks a lot guys...instead of using HAVING, i used where distance < 40, and the query runs a lot faster...

  5. #5
    Join Date
    Mar 2010
    Posts
    672
    Always run an "explain" when developing a query. To do this simply place the keyword "explain" before the query. This will give you an estimate of the rows the query is going through to retrieve the records. Generally if the estimate row counts are greater than the records to be returned by the query then you have some optimization that can be done. Though this rule isn't true for items like ranges (aka < or >) or like statements (aka like '&#37;text%'), as those force the dbms to scan more of the table than would otherwise be needed by more strict/concrete statements in the where clause. Using the explain statement will show you these issues. It is also useful when you're developing a query (a non-dangerous select query) on a live db, as explain will generally show you that there is an error in the query without messing up the system, where you can easily bring down an entire database if you run the actual query on a table that only supports full table locking (update statements will get queued while the select statement stalls, which stalls all other select statements...etc). And lastly, can't stress this enough, index your tables properly. You really only need to index the fields that are used within your where and have clauses. Just be sure not to over index as each index needs to be updated along with the record every time there is an update to the table, but this is only an issue if you update a lot too.

    Edit: Forgot to mention that even if your query is now running a lot faster, i'd still go through the steps to try and optimize it as much as possible. That way you will be sure your query will scale the best it can as you get more traffic using it.
    Last edited by Jarrod1937; 09-19-2010 at 06:51 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles