Hottospot

I have started experiencing issues with the plugin which results in high CPU and high SQL memory which is causing problems with one of my sites that has 7000+ posts and 7 pages.

Looking through my Slow Query logs the file is populated with numerous wp_yarpp_related_cache related queries.

What is worrying is the number of records affected (running into tens of millions!) e.g on just one days slow query log file I have literally hundreds of instances of the

insert into wp_yarpp_related_cache (reference_ID,ID,score)

query in my log file with stats such as the following:

# Query_time: 16 Lock_time: 0 Rows_sent: 0 Rows_examined: 12184905
# Query_time: 26 Lock_time: 0 Rows_sent: 0 Rows_examined: 21898590
# Query_time: 21 Lock_time: 0 Rows_sent: 0 Rows_examined: 17727610
# Query_time: 9 Lock_time: 0 Rows_sent: 0 Rows_examined: 6533365
# Query_time: 7 Lock_time: 0 Rows_sent: 0 Rows_examined: 4291965

Also on further analysis of the query which is being logged I can see that you are doing a UNION statement with a number of LEFT JOINS.

However both parts of the UNION are exactly the same!

I am not sure why you need to do this as I have tested the query out myself and you can remove either half of the UNION to get the same results. Maybe there is a need for the UNION if the criteria differs on each side but in the case where its the same you should change the query as they are very performance intensive and doing each one twice when there is no need is causing a lot of problems.

For example this is just one of the many queries from the log file I have found where both the top and bottom half of the UNION is the same

insert into wp_yarpp_related_cache (reference_ID,ID,score)

(SELECT 10309, ID, (0+ (MATCH (post_content) AGAINST (‘euro global banks dollars world economic program debt greece crisis taxpayers bailout european governance financial currency dollar states money single ‘)) * 1+ (MATCH (post_title) AGAINST (‘euro bail looted taxpayers american ‘)) * 3+ 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 (29,1497))
left JOIN wp_term_relationships AS thistag ON (thistag.object_id = 10309 )
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 = 10309 )
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 != ‘10309’) and post_password =”
group by id
having score >= 2.50 and count(blockterm.term_id) = 0 order by score desc limit 5)

union

(SELECT 10309, ID, (0+ (MATCH (post_content) AGAINST (‘euro global banks dollars world economic program debt greece crisis taxpayers bailout european governance financial currency dollar states money single ‘)) * 1+ (MATCH (post_title) AGAINST (‘euro bail looted taxpayers american ‘)) * 3+ 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 (29,1497))
left JOIN wp_term_relationships AS thistag ON (thistag.object_id = 10309 )
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 = 10309 )
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 != ‘10309’) and post_password =”
group by id
having score >= 2.50 and count(blockterm.term_id) = 0 order by score desc limit 5)

on duplicate key update date = now();

Maybe you can look into this?