Custom SQL query slows down when using multiple OR ... LIKE ... in posts_where filter

admin管理员组

文章数量:1287970

I have built a posts query that includes a custom table in search using the posts_join and posts_where filters.

Now the customer wants me to include yet another custom table in the search, but doing so increases query time from an instant to over 5 seconds.

This is what I have done:

function my_posts_join( $join ) {
  global $wpdb;
  $old_table = "wp_some_custom_table";
  $new_table = "wp_another_custom_table";
  if ( is_search() ) {
      $join .=' LEFT JOIN '.$wpdb->postmeta. ' ON '. $wpdb->posts . '.ID = ' . $wpdb->postmeta . '.post_id ';
      $join .=' LEFT JOIN '.$old_table. '  ON '. $wpdb->posts . '.ID = ' . $old_table . '.post_id ';
      $join .=' LEFT JOIN '.$new_table. '  ON '. $wpdb->posts . '.ID = ' . $new_table . '.post_id ';
  }
  return $join;
}

The additional join doesn't noticeably affect performance, but when I edit the posts_where clause like this...

function my_posts_where( $where ) {
  global $wpdb;
  $old_table = "wp_some_custom_table";
  $new_table = "wp_another_custom_table";
  if ( is_search() ) {
    $where = preg_replace(
        "/\(\s*".$wpdb->posts.".post_title\s+LIKE\s*(\'[^\']+\')\s*\)/",
        "(".$wpdb->posts.".post_title LIKE $1) 
          OR (".$wpdb->postmeta.".meta_value LIKE $1) 
          OR (".$old_table.".text LIKE $1) 
          OR (".$new_table.".text LIKE $1)", // <-- this kills it
        $where );
  }
  return $where;
}

The additional OR condition slows everything to a crawl.

Any ideas how to speed this up or structure the query differently?

So far I have indexes on post_id in both custom tables. Any other indexes that would help?

Also I have read the advice to break up OR conditions into multiple queries and use UNION on the results, but I have no idea how this would be possible in the context of WordPress filters.

本文标签： Custom SQL query slows down when using multiple ORLIKEin postswhere filter