Too many reverse term query and Database query load

  • calvin

    (@calvinorenda)


    5 months ago

    I have noticed that there are many reverse term queries generated. I have over 20,000 post with Titles of of only two words, example: Apple Tree

    and I notice every month or so, Relevanssi will generate tons of query using searching for inverse term, example “Tree Apple”, also tons of term relationship queries, this is generating queries so fast that it overload my db and it will overload the queries every 15 minutes for 2 to 3 days. It also generated tons of (meaningless IMO) entries like “elppA”, or “segnarO” (Apple, Oranges in reverse) in the Relevanssi tables.

    SELECT ID FROM wp_posts WHERE post_title = ‘Tree Apple’ AND post_type = ‘product’

    also running a lot of

    SELECT wp_posts.ID FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_term_relationships AS tt1 ON (wp_posts.ID = tt1.object_id) WHERE 1=1 AND ( wp_posts.post_date < ‘xxx’ ) AND ( wp_term_relationships.term_taxonomy_id IN (xx) AND tt1.term_taxonomy_id IN (xxx,xxx) ) AND wp_posts.post_type = ‘post’ AND ((wp_posts.post_status = ‘publish’)) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 2

    Any suggestion on this issue to reduce the load?
    Is the reverse title query “Tree Apple” or tons of reverse entries in Relevanssi tables necessary and can be turned off or removed?

    • This topic was modified 5 months ago by calvin.
    • This topic was modified 5 months ago by calvin.
    • This topic was modified 5 months ago by calvin. Reason: typo
Viewing 1 replies (of 1 total)
  • Plugin Author Mikko Saari

    (@msaari)

    Are you sure those are Relevanssi queries? Relevanssi doesn’t query wp_post like that, and doesn’t do any reverse term queries. Relevanssi always queries one word at a time: “apple tree” would always be queried as “apple” and “tree” separately. That must be coming from something else.

    The Relevanssi table has reverse words, because it’s faster to do LIKE matching at the end than the beginning of the word: querying “%bcd” is much slower than “dcb%”. These are very much necessary and aren’t a big deal performance-wise in any case.

    Usually, Relevanssi performance issues that relate to filters that run for every posts search and involve a database query – for example, doing a get_post_meta() for each post found. These can usually be optimized to one database query that gets the value for each post and stores it in memory.

Viewing 1 replies (of 1 total)
  • You must be logged in to reply to this topic.