我有一个MySQL查询，用于处理WordPress中相当复杂的搜索，因为我一直在努力让wp_query做我需要它做的一切。
然而，有时查询需要很长时间才能运行（有时需要10秒以上！），有时似乎会导致服务器崩溃（media temple Grid），返回一个内部服务器错误或一个无法建立数据库连接的错误。
我不确定查询中是否存在语法错误导致它崩溃，但生成查询的PHP大致如下：
如果有任何想法，请告诉我！如果需要更多信息，我很乐意提供：）
编辑：
当搜索中有关键词时，查询似乎会遇到更多问题。我不确定在写逻辑时是否有更好的方法：
然而，有时查询需要很长时间才能运行（有时需要10秒以上！），有时似乎会导致服务器崩溃（media temple Grid），返回一个内部服务器错误或一个无法建立数据库连接的错误。
我不确定查询中是否存在语法错误导致它崩溃，但生成查询的PHP大致如下：
<?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还比较新，不太清楚如何优化这个过程 :/
$events = $wpdb->get_results( $query, 'OBJECT' );之前将生成的查询附加到日志文件中。通过这个日志，你可以尝试使用EXPLAIN来优化查询。 - VMai