mySQL Query seems to crash server

Question

I have a mySQL Query to handle a fairly complicated search in WordPress because I've been struggling to get wp_query to do everything I need it to.

However, sometimes the query takes a long time to run (10+ seconds sometimes!) and sometimes it seems to just crash the server (media temple Grid), returning a internal server error or an Error establishing database connection.

I'm not sure if there's a general syntax error in the query that's causing it to crash or not, but essentially the PHP that generates the query looks like this:

<?php

// declare wordpress database global
global $wpdb;

// order by option
$order = $_SESSION['search']['sort-by'];

// users lat, long and distance preferences
$lat = $_SESSION['search']['lat'];
$long = $_SESSION['search']['long'];
$radius = $_SESSION['search']['distance'];

// user search start/end date
$startDate = date('Ymd', strtotime($_SESSION['search']['from']));
$endDate = date('Ymd', strtotime($_SESSION['search']['to']));

// get the main category search ID
$maincat = get_term_by( 'slug', $_SESSION['search']['cat'], 'main-cat');
$maincat = $maincat->term_taxonomy_id;

// grab keywords, replace special chars and spaces
$keywords = $_SESSION['search']['keyword'];
$keywords = preg_replace('/[^A-Za-z0-9 ,]/u','', strip_tags($keywords));
$keywords = str_replace(' ', '', $keywords);

// put keywords into array
$subcatItems = explode(',', $keywords);
$keywords = $subcatItems;

// for each keywords get the sub category id's
$subcats = array();
$count = count($subcatItems) - 2;
for ($i = 0; $i <= $count; $i++) {
    $subcatItems[$i] = get_term_by( 'slug', $subcatItems[$i], 'sub-cat');
    if ($subcatItems[$i] != '') : 
        $subcatItems[$i] = $subcatItems[$i]->term_taxonomy_id;
        array_push($subcats, $subcatItems[$i]);
    endif;
}
if( $subcats != '' ) :
    $subcats = implode(',', $subcats);
endif;



// select
$query = 'SELECT SQL_CALC_FOUND_ROWS wp_posts.*, ';


// geo locate
$query .= '( 3959 * acos(
     cos( radians('.$lat.') ) 
     * cos( radians( lat ) ) 
     * cos( radians( lng ) - radians('.$long.') ) 
     + sin( radians('.$lat.') ) 
     * sin( radians( lat ) ) 
     ) ) 
 AS distance , lat AS  latitude , lng AS longitude ';


 // from
 $query .= 'FROM wp_posts ';


 // inner joins
 $query .= 'INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) ';
 if ( $keywords != '' ) :
    $query .= 'INNER JOIN wp_term_relationships AS tt1 ON (wp_posts.ID = tt1.object_id) ';
 endif;
 $query .= 'INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) ';
 $query .= 'INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) ';

 // if ordered by price, join post meta again
 if( $order == 'mt2.meta_value+0' ) :
     $query .= 'INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id) ';
 endif;

 // if there are keywords 
 if ( $keywords != '' ) :
    $query .= 'INNER JOIN wp_postmeta AS mt3 ON (wp_posts.ID = mt3.post_id) ';
 endif;

 // join table to geo locate
 $query .= 'INNER JOIN lat_lng_post ON wp_posts.ID = lat_lng_post.post_id ';


 // basic filter
 $query .= 'WHERE 1=1 ';
 $query .= 'AND wp_posts.post_type = "event" '; 
 $query .= 'AND wp_posts.post_status = "publish" ';


 // geo filter
 $query .= 'AND lat_lng_post.lat = lat ';
 $query .= 'AND lat_lng_post.lng = lng ';


 // date filter
 $query .= 'AND ( '; 
 $query .= '(wp_postmeta.meta_key LIKE "date_%_start-date" AND CAST(wp_postmeta.meta_value AS SIGNED) <= "'.$endDate.'") ';
 $query .= 'AND (mt1.meta_key LIKE "date_%_end-date" AND CAST(mt1.meta_value AS SIGNED) >= "'.$startDate.'") ';
 $query .= 'AND substr(wp_postmeta.meta_key, 1, 6) = substr(mt1.meta_key, 1, 6)  ';
 $query .= ') ';


 // taxonomies filter
 $query .= 'AND ( wp_term_relationships.term_taxonomy_id IN ('.$maincat.') ) ';

 // if keywords
 if ( $_SESSION['search']['keyword'] != '' ) :
    $query .= 'AND ( ';

    // for each keyword, and a statement to check post title
    $keywordCount = 0;
    foreach ( $keywords as $keyword ) :
        if( $keyword != '' ) :
            if( $keywordCount == 0 ) :
                $query .= '(wp_posts.post_title LIKE "%'.$keyword.'%") ';
            else :
                $query .= 'OR (wp_posts.post_title LIKE "%'.$keyword.'%") ';
            endif;
        endif;
        $keywordCount++;
    endforeach;

    // for each keyword, and a statement to check description
    foreach ( $keywords as $keyword ) :
        if( $keyword != '' ) :
            $query .= 'OR (mt3.meta_key = "description" AND mt3.meta_value LIKE "%'.$keyword.'%") ';
        endif;
    endforeach;

    // for each keyword, and a statement to check sub category taxonomy
    if( $subcats != '' ) :
        $query .= 'OR ( tt1.term_taxonomy_id IN ('.$subcats.') )';
    endif;

    $query .= ') ';
 endif;     


 // if ordered by adult
 if( $order == 'mt2.meta_value+0' ) :
     $query .= 'AND mt2.meta_key = "adult" ';
 endif;

 // grouping and sorting
 $query .= 'GROUP BY wp_posts.ID ';
 $query .= 'HAVING distance <= '.$radius.' ';
 $query .= 'ORDER BY '.$order.' ASC ';
 $query .= 'LIMIT 0, 10';

 $events = $wpdb->get_results( $query, 'OBJECT' ); 

?>

If anyone has any ideas, please let me know! And if you need any more information, I'm happy to supply it :)

EDIT

The Query seems to struggle a lot more when there are keywords in the search. I'm not sure if there's a better way of writing the logic around :

if ( $_SESSION['search']['keyword'] != '' ) :
    $query .= 'AND ( ';

    // for each keyword, and a statement to check post title
    $keywordCount = 0;
    foreach ( $keywords as $keyword ) :
        if( $keyword != '' ) :
            if( $keywordCount == 0 ) :
                $query .= '(wp_posts.post_title LIKE "%'.$keyword.'%") ';
            else :
                $query .= 'OR (wp_posts.post_title LIKE "%'.$keyword.'%") ';
            endif;
        endif;
        $keywordCount++;
    endforeach;

    // for each keyword, and a statement to check description
    foreach ( $keywords as $keyword ) :
        if( $keyword != '' ) :
            $query .= 'OR (mt3.meta_key = "description" AND mt3.meta_value LIKE "%'.$keyword.'%") ';
        endif;
    endforeach;

    // for each keyword, and a statement to check sub category taxonomy
    if( $subcats != '' ) :
        $query .= 'OR ( tt1.term_taxonomy_id IN ('.$subcats.') )';
    endif;

    $query .= ') ';
 endif;

EDIT 2

Another thought I've just had, would it be quicker to split the query up into separate queries perhaps? So just complete the geo query first, take those post ID's and do the date query, then do the keywords query perhaps? I'm quite new to mySQL so not too sure how to optimise this :/

Answer 1

Try to add the final conditions to the joins (multiple join conditions).

If you use multiple conditions at joins instead of get a huge set and then put the condition at the end your set will be smaller and your response time may be better.

for instance, you have a final condition:

AND wp_posts.post_type = "event"

You could put it in the first join

INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) AND wp_posts.post_type = "event"
INNER JOIN...

and so on.