I’m trying to change a query over so that it runs on a new table to help speed up a website. It’s a Geolocation query that finds events within a certain distance of the users lat/long.

The script itself is fine, it’s just that the lat/long are stored in a massive table that take s a while to go through, moving it to its own table should speed things up I think.

Anyway, here’s the original query:

SELECT DISTINCT wp_posts.*, 
( 3959 * acos(
    cos( radians(%s) ) *
    cos( radians( latitude.meta_value ) ) *
    cos( radians( longitude.meta_value ) - radians(%s) ) +
    sin( radians(%s) ) *
    sin( radians( latitude.meta_value ) )
) ) 
AS distance , 
latitude.meta_value AS latitude , 
longitude.meta_value AS longitude 
FROM wp_posts 
INNER JOIN wp_postmeta AS latitude ON wp_posts.ID = latitude.post_id 
INNER JOIN wp_postmeta AS longitude ON wp_posts.ID = longitude.post_id 
WHERE 1=1 
AND wp_posts.post_type = 'event' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
AND latitude.meta_key="lat"
AND longitude.meta_key="long" 
HAVING distance <= 15 
ORDER BY distance ASC, wp_posts.post_date DESC

Instead of querying wp_posts I want to query a new table called ‘lat_lng_post’ which just has three columns post_id, lat and lng.

Here’s my attempt at changing this query:

global $wpdb; 
$results = $wpdb->query( $wpdb->prepare("
    SELECT post_id, 
    ( 3959 * acos(
         cos( radians(%s) ) 
         * cos( radians( lat ) ) 
         * cos( radians( lng ) - radians(%s) ) 
         + sin( radians(%s) ) 
         * sin( radians( lat ) ) 
         ) ) 
    AS distance , lat AS latitude , lng AS longitude FROM lat_lng_post 
    WHERE 1=1 
    AND lat=%s
    AND lng=%s
    HAVING distance <= %d 
    ORDER BY distance ASC
    ",
    $_SESSION['search']['lat'],
    $_SESSION['search']['long'],
    $_SESSION['search']['lat'],
    $_SESSION['search']['lat'],
    $_SESSION['search']['long'],
    $_SESSION['search']['distance']));  

It’s not actually returning any errors, but also it’s not returning any events at all.

Read more here: Change Query to run on different db table


Solution:

If you know the solution of this issue, please leave us a reply in Comment section, to update the question.

Related Wordpress search:

, , , ,

Wordpress related questions and answers: