• Hi

    I use Job Manager plugin and I want to search for the locations that their distance are less than 10 Kilometers from input $lang and $lat. I use following query to do this, but it takes many time (more than 6 seconds) to respond. Do you know a better way or better sql query to do this?

    $wpdb->prepare( "
               SELECT DISTINCT
               map_lat.post_id,
               p.post_title,
               map_lat.meta_value as locLat,
               map_lng.meta_value as locLong,
                (
       6371 * ACOS(
         COS(RADIANS( %s )) * COS(RADIANS(map_lat.meta_value)) * COS(
           RADIANS(map_lng.meta_value) - RADIANS( %s )
         ) + SIN(RADIANS( %s )) * SIN(RADIANS(map_lat.meta_value))
       )
     ) AS distance
    FROM $wpdb->posts p
           INNER JOIN $wpdb->postmeta map_lat ON p.ID = map_lat.post_id
           INNER JOIN $wpdb->postmeta map_lng ON p.ID = map_lng.post_id
           WHERE 1 = 1
           AND p.post_type = 'job_listing'
           AND p.post_status = 'publish'
           AND map_lat.meta_key = 'geolocation_lat'
           AND map_lng.meta_key = 'geolocation_long' 
           HAVING distance < %s
           ORDER BY map_lat.post_id DESC LIMIT 5",
                $lat,
                $long,
                $lat,
                $radius
            );
Viewing 2 replies - 1 through 2 (of 2 total)
  • Moderator bcworkz

    (@bcworkz)

    There are two things slowing down your query. One is requiring relatively complex trigonometry for every record searched. Two is searching for meta values by key value.

    To avoid trigonometry in queries, pre-determine the lat/long bounds and query simply by greater than/less than, which would give you results within a square bounds instead of a radius. If this crude approximation is too generous, post process the results in PHP to throw out results within the square bounds that are out of the prescribed radius.

    To avoid meta key queries, build a separate table for lat/long values where each has their dedicated column. Relate the table back to posts by ID and table JOIN relations. Restructuring tables could seem like a monumental task, but a one time script to copy over values into anther table and remove the original could be developed without too much work.

    Consider using this custom lat/long table for other meta values you frequently need to query for, each meta key value having its own column.

    Dion

    (@diondesigns)

    The problem in your query is this line:

    HAVING distance < %s
    

    The HAVING clause is not optimized by MySQL, but even if it was, the distance value contains columns that aren’t indexed. That is guaranteed to cause slow queries.

    My suggestion is to remove the HAVING clause and distance value from your query, and instead read in all data. You can then loop through the results and do the trigonometry within PHP to determine whether a row should be used.

Viewing 2 replies - 1 through 2 (of 2 total)

The topic ‘high response time for a custom query’ is closed to new replies.