WordPress.org

Ready to get started?Download WordPress

Forums

[Plugin: Yet Another Related Posts Plugin] yarpp slow queries (4 posts)

  1. futtta
    Member
    Posted 4 years ago #

    I had been experiencing slower performance on my blog (438 Posts, 5 Pages, 36 Categories and 712 Tags, hosted on a low-spec virtual server) since approx the end of November.

    I activated slow query logging on mysql and found the one query (example below) was consistently slow:

    # Time: 100115 13:55:03
    # User@Host: wp @ localhost []
    # Query_time: 3 Lock_time: 0 Rows_sent: 0 Rows_examined: 138830
    SET timestamp=1263560103;
    insert into wp_yarpp_related_cache (reference_ID,ID,score) (SELECT 130, ID, (0+ (MATCH (post_content) AGAINST ('de ik en op een safari niet van voor windows ook met dat te maar als mijn al zijn beta ')) * 3+ (MATCH (post_title) AGAINST ('updated wiki ate safari ')) * 1+ COUNT( DISTINCT tagtax.term_taxonomy_id ) * 1+ COUNT( DISTINCT cattax.term_taxonomy_id ) * 1) as score
    from wp_posts
    left join wp_term_relationships as blockrel on (wp_posts.ID = blockrel.object_id)
    left join wp_term_taxonomy as blocktax using (term_taxonomy_id)
    left join wp_terms as blockterm on (blocktax.term_id = blockterm.term_id and blockterm.term_id in (152,614))
    left JOIN wp_term_relationships AS thistag ON (thistag.object_id = 130 )
    left JOIN wp_term_relationships AS tagrel on (tagrel.term_taxonomy_id = thistag.term_taxonomy_id
    AND tagrel.object_id = wp_posts.ID)
    left JOIN wp_term_taxonomy AS tagtax ON ( tagrel.term_taxonomy_id = tagtax.term_taxonomy_id
    AND tagtax.taxonomy = 'post_tag')
    left JOIN wp_term_relationships AS thiscat ON (thiscat.object_id = 130 )
    left JOIN wp_term_relationships AS catrel on (catrel.term_taxonomy_id = thiscat.term_taxonomy_id
    AND catrel.object_id = wp_posts.ID)
    left JOIN wp_term_taxonomy AS cattax ON ( catrel.term_taxonomy_id = cattax.term_taxonomy_id
    AND cattax.taxonomy = 'category')
    where (post_status IN ( 'publish', 'static' ) and ID != '130') and post_password ='' and post_date > date_sub(now(), interval 12 month)
    group by id
    having score >= 3.50 and count(blockterm.term_id) = 0 and COUNT( DISTINCT cattax.term_taxonomy_id ) >= 1 order by score desc limit 5) union (SELECT 130, ID, (0+ (MATCH (post_content) AGAINST ('de ik en op een safari niet van voor windows ook met dat te maar als mijn al zijn beta ')) * 3+ (MATCH (post_title) AGAINST ('updated wiki ate safari ')) * 1+ COUNT( DISTINCT tagtax.term_taxonomy_id ) * 1+ COUNT( DISTINCT cattax.term_taxonomy_id ) * 1) as score
    from wp_posts
    left join wp_term_relationships as blockrel on (wp_posts.ID = blockrel.object_id)
    left join wp_term_taxonomy as blocktax using (term_taxonomy_id)
    left join wp_terms as blockterm on (blocktax.term_id = blockterm.term_id and blockterm.term_id in (152,614))
    left JOIN wp_term_relationships AS thistag ON (thistag.object_id = 130 )
    left JOIN wp_term_relationships AS tagrel on (tagrel.term_taxonomy_id = thistag.term_taxonomy_id
    AND tagrel.object_id = wp_posts.ID)
    left JOIN wp_term_taxonomy AS tagtax ON ( tagrel.term_taxonomy_id = tagtax.term_taxonomy_id
    AND tagtax.taxonomy = 'post_tag')
    left JOIN wp_term_relationships AS thiscat ON (thiscat.object_id = 130 )
    left JOIN wp_term_relationships AS catrel on (catrel.term_taxonomy_id = thiscat.term_taxonomy_id
    AND catrel.object_id = wp_posts.ID)
    left JOIN wp_term_taxonomy AS cattax ON ( catrel.term_taxonomy_id = cattax.term_taxonomy_id
    AND cattax.taxonomy = 'category')
    where (post_status IN ( 'publish', 'static' ) and ID != '130') and post_password ='' and post_date > date_sub(now(), interval 12 month)
    group by id
    having score >= 3.50 and count(blockterm.term_id) = 0 and COUNT( DISTINCT cattax.term_taxonomy_id ) >= 1 order by score desc limit 5) on duplicate key update date = now();

    I finally changed configuration to not take my 'tags' into account any more to solve this slowdown.

    my questions;
    -> was the new cache building functionality indeed introduced the end of november?
    -> can the above query be optimized so tags can be taken into account again?

    http://wordpress.org/extend/plugins/yet-another-related-posts-plugin/

  2. Gravitydk
    Member
    Posted 4 years ago #

    I have also found this query has a very long execution time. I discovered it by using the plugin WPDB Profiler, which shows it as the worst of all the queries run when a page is displayed.

    insert into wptable_yarpp_related_cache (reference_ID,ID,score) SELECT 2594, ID, (0+ (MATCH (post_content) AGAINST ('fast threat slow gear damage nerubian tank gain fc dps its weapons parry weapon avoidance rune runeforges total hand vs ')) * 1+ (MATCH (post_title) AGAINST ('wield threat dual effect speed weapon ')) * 1+ COUNT( DISTINCT tagtax.term_taxonomy_id ) * 1+ COUNT( DISTINCT cattax.term_taxonomy_id ) * 1) as score from wptable_posts left JOIN wptable_term_relationships AS thistag ON (thistag.object_id = 2594 ) left JOIN wptable_term_relationships AS tagrel on (tagrel.term_taxonomy_id = thistag.term_taxonomy_id AND tagrel.object_id = wptable_posts.ID) left JOIN wptable_term_taxonomy AS tagtax ON ( tagrel.term_taxonomy_id = tagtax.term_taxonomy_id AND tagtax.taxonomy = 'post_tag') left JOIN wptable_term_relationships AS thiscat ON (thiscat.object_id = 2594 ) left JOIN wptable_term_relationships AS catrel on (catrel.term_taxonomy_id = thiscat.term_taxonomy_id AND catrel.object_id = wptable_posts.ID) left JOIN wptable_term_taxonomy AS cattax ON ( catrel.term_taxonomy_id = cattax.term_taxonomy_id AND cattax.taxonomy = 'category') where (post_status IN ( 'publish', 'static' ) and ID != '2594') and post_password ='' and post_date > date_sub(now(), interval 6 month) group by id having score >= 4.00 order by score desc limit 6 on duplicate key update date = now()

  3. Gravitydk
    Member
    Posted 4 years ago #

    Here's the solution. Change plugins. This alternative has been coded for efficiency.
    YARPP is hitting my CPU really badly, and the authors' current recommendation is to disable TAG weightings, which reduces its accuracy for my site too much.

    http://wordpress.org/extend/plugins/efficient-related-posts/

  4. As Gravitydk mentions, I suggest you disable tag and category considerations, as well as not excluding any tags or categories. futtta, turning off "cross related posts and pages" would also help.

    Over time, though, due to the cache, these queries should not be running on every request, though.

Topic Closed

This topic has been closed to new replies.

About this Topic