php网站搜索 经常崩溃,php – mySQL查询似乎崩溃了服务器

我有一个mySQL查询来处理WordPress中相当复杂的搜索,因为我一直在努力让wp_query做我需要的所有事情.

但是,有时查询需要很长时间才能运行(有时需要10秒！),有时它似乎只是使服务器崩溃(媒体模板网格),返回内部服务器错误或建立数据库连接时出错.

我不确定查询中是否存在导致其崩溃的常规语法错误,但实质上生成查询的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 ( $_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;

编辑2

我刚刚想到的另一个想法是,将查询拆分成单独的查询可能会更快吗？那么首先完成地理查询,获取那些帖子ID并进行日期查询,然后再进行关键字查询？我对mySQL很新,所以不太确定如何优化它：/