Wordpress include post associated to specific term in search results

Question

I'm working on a custom search engine for my WordPress site. The DB for the project is pretty big ( 300k+ posts ) so performance is key.

I have a searchbar returning posts ('clients' & 'professionnels') and i'm trying to limit search results to post title and terms name from 'specialites' taxonomy. So far i'm using a filter (https://stackoverflow.com/a/59537500/19181295).

function and_extend_search( $search, &$wp_query ) {

global $wpdb;

if ( empty( $search ))
    return $search;

$terms = $wp_query->query_vars[ 's' ];
$exploded = explode( ' ', $terms );
if( $exploded === FALSE || count( $exploded ) == 0 )
    $exploded = array( 0 => $terms );

$search = '';

foreach( $exploded as $tag ) {
    $search .= " AND (
        ($wpdb->posts.post_title LIKE '%$tag%')

        OR EXISTS
        (
            SELECT 
                *
            FROM 
                $wpdb->term_relationships 
            LEFT JOIN 
                $wpdb->terms 
            ON 
                $wpdb->term_relationships.term_taxonomy_id = $wpdb->terms.term_id
            WHERE
                    $wpdb->terms.name LIKE '%$tag%'
                AND
                    $wpdb->term_relationships.object_id = $wpdb->posts.ID
        )
    )";
}

return $search;

} add_filter('posts_search', 'and_extend_search', 500, 2);

The filter is pretty good but it's searching inside all taxonomies. What i want is keeping the filter but only return results in terms associated with 'specialites' taxonomy.

Can you guys help me please ? :)

from Comment

CREATE TABLE xmo_term_relationships (
    object_id bigint(20) unsigned NOT NULL DEFAULT 0, 
    term_taxonomy_id bigint(20) unsigned NOT NULL DEFAULT 0, 
    term_order int(11) NOT NULL DEFAULT 0, 
    PRIMARY KEY (object_id,term_taxonomy_id), 
    KEY term_taxonomy_id (term_taxonomy_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 

Answer 1

If the tables are big and you want performance,

• Do not use LIKE with leading wildcards
• Do consider using FULLTEXT index(es).
• Do install this plugin: WP Index Improvements
• Get rid of the second if statement; $exploded will be as you want it anyway.
• Try to avoid ORs; they do not Optimize well.

After you have done those, come back with some revised queries; I will probably have more suggestions. At that time, please provide SHOW CREATE TABLE and EXPLAIN.