Another MySQL Slow Query

  • 762mm

    (@762mm)


    2 years ago

    I’m getting another type of slow DB query. These are of the form:

    SELECT DISTINCT(relevanssi.doc), 1 AS term, 1 AS term_reverse,
                1 AS content, 1 AS title, 1 AS comment, 1 AS tag, 1 AS link, 1 AS
                author, 1 AS category, 1 AS excerpt, 1 AS taxonomy, 1 AS customfield,
                1 AS mysqlcolumn, 1 AS taxonomy_detail, 1 AS customfield_detail, 1 AS
                mysqlcolumn_detail, type, item, 1 AS tf
                FROM wp_relevanssi AS relevanssi
                WHERE relevanssi.term = relevanssi.term  AND relevanssi.doc NOT IN (0,46917,46972,46977,4903, <...> ,56876,56889,56901,120435,128347) AND ((relevanssi.doc IN (SELECT DISTINCT(posts.ID) FROM wp_posts AS posts
                                WHERE posts.post_status IN ('publish'))) OR (doc = -1)) GROUP BY doc, item, type ORDER BY doc ASC LIMIT 500;
Viewing 5 replies - 1 through 5 (of 5 total)
  • Plugin Author Mikko Saari

    (@msaari)

    It’s still a search without a search term. Have you perhaps introduced some excluded posts to the search? This query has post exclusions in it; otherwise, it’s just a normal search query without a search term.

    Thread Starter 762mm

    (@762mm)

    These were happening before, I just didn’t notice because they were relatively rare compared to the other formats of slow queries. I’m guessing they are coming from the bad URLs Googlebot is crawling.

    Should this code that blocks the other queries with no search term block these? 

    add_filter( 'relevanssi_search_ok', function( $ok, $query ) {
  return empty( $query->query_vars['s'] ) ? false : $ok;
}, 10, 2 );

    All of them seem to have the same post exclusions, but I don’t know where they would come from. Assuming those are post IDs, there are 683 of them in the query, but only 11 of them correspond to actual posts in wp_posts. Those 11 posts don’t appear to be related to each other in any way.

    Plugin Author Mikko Saari

    (@msaari)

    Yes, that code should also stop these. It stops Relevanssi from performing searches unless a search term is defined, and here there isn’t one.

    Thread Starter 762mm

    (@762mm)

    Given that I have that code in functions.php and these searches are still happening (yet the other ones with no search term from my previous are not), where could these be originating from?

    Plugin Author Mikko Saari

    (@msaari)

    I’d look into the server access logs to see where these are coming from, what kind of searches are triggering these and who’s making them.

    With the code in place, Relevanssi should stop all queries that don’t have a search term from triggering Relevanssi; they should only trigger the WordPress default search.

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Another MySQL Slow Query’ is closed to new replies.