Skip to:
Content

bbPress.org

Opened 2 years ago

Last modified 17 months ago

#3156 new idea

Additional filter for the search function

Reported by: januzi_pl Owned by:
Milestone: 2.7 Priority: low
Severity: major Version:
Component: Component - Search Keywords: needs-patch
Cc:

Description

Right now search function is doing things like this:

# Query_time: 2.472832  Lock_time: 0.000098 Rows_sent: 15  Rows_examined: 631204
SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND 
(((wp_posts.post_title LIKE '%junak%') OR (wp_posts.post_excerpt LIKE '%junak%')
 OR (wp_posts.post_content LIKE '%junak%')))  AND wp_posts.post_type IN ('forum', 'topic', 'reply') 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'closed' 
OR wp_posts.post_status = 'reported' OR wp_posts.post_author = 5 
AND wp_posts.post_status = 'private' OR wp_posts.post_author = 5 
AND wp_posts.post_status = 'hidden')  ORDER BY wp_posts.post_date DESC LIMIT 0, 15;

General rule for the efficient mysql query is not to use LIKE %% (because mysql will have to parse every single record), so I'm proposing a simple and rather painless way of dealing with this problem.

A new search filter. It could work somewhat like that:

<?php
$post_id_in = '' ;
$post_id_in = apply_filter( 'bbpress_search_by_post_id', $default_search_terms ) ;
if( !empty( $post_id_in )) {
  // sanitize $post_id_in so there are only numbers
  ...
  // pass it into the query
  $default['post__in'] = $post_id_in ; 
}

This way Sphinx could be used without editing the core files. With that in mind, this is how Explain shows the same query, but with the wp_posts.ID in ( ) added into the where:

table type key rows extra
wp_posts range PRIMARY 15 Using where; Using filesort

With more matches the rows number will be higher, but still not as much as 600k (or 9M). We could also pass bbp_get_paged() into the filter, so the function could just slice proper part of the array.

Change History (1)

#1 @johnjamesjacoby
17 months ago

  • Keywords needs-patch added
  • Milestone changed from Awaiting Review to 2.7
Note: See TracTickets for help on using tickets.