We've been having some database issues since starting to use YARPP.
Our /tmp directory starting filling up and eventually all the *.MYI tables crashed and had to be repaired.
Now the mysql error log is filled with entries like this one:
110723 9:28:03 [Warning] Statement may not be safe to log in statement format. Statement: insert into wp_yarpp_related_cache (reference_ID,ID,score) (SELECT 49640 as reference_ID, ID, ROUND(0+ (MATCH (post_content) AGAINST ('patients patient doctor doctors communicate physician tell thought communication remember people says physicians paul may dorio hospitalized dont specialist said ')) * 3+ (MATCH (post_title) AGAINST ('say patients doctors attention close pay ')) * 3,1) as score
from wp_posts
where (post_status IN ( 'publish', 'static' ) and ID != '49640') and post_password ='' and post_
110723 9:28:25 [Warning] Statement may not be safe to log in statement format. Statement: insert into wp_yarpp_related_cache (reference_ID,ID,score) (SELECT 24754 as reference_ID, ID, ROUND(0+ (MATCH (post_content) AGAINST ('yikes ')) * 3+ (MATCH (post_title) AGAINST ('fattest man book guinness glad im ')) * 3,1) as score
from wp_posts
where (post_status IN ( 'publish', 'static' ) and ID != '24754') and post_password ='' and post_date > date_sub(now(), interval 18 month) and post_type = 'post'
group by ID
having score >= 5.00 order by score desc limit 6) union (SELECT 24754 as reference
I've also noticed a large increase of joins not using indexes so I started logging them in the slow query log and it's filled with entries like this:
# User@Host: xxxxxxx.xx[xxxxxx_wp] @ localhost []
# Query_time: 0.035477 Lock_time: 0.000111 Rows_sent: 0 Rows_examined: 8831
SET timestamp=1311439559;
insert into wp_yarpp_related_cache (reference_ID,ID,score) (SELECT 49994 as reference_ID, ID, ROUND(0+ (MATCH (post_content) AGAINST ('guidelines chronic pelvic antibiotics alpha pain mrsa dont oa treating icds guideline evidence practice prevention standard primary disregarding manage painkillers ')) * 3+ (MATCH (post_title) AGAINST ('january 2011 morning medicine stories health top ')) * 3,1) as score
from wp_posts
where (post_status IN ( 'publish', 'static' ) and ID != '49994') and post_password ='' and post_date > date_sub(now(), interval 18 month) and post_type = 'post'
group by ID
having score >= 5.00 order by score desc limit 6) union (SELECT 49994 as reference_ID, ID, ROUND(0+ (MATCH (post_content) AGAINST ('guidelines chronic pelvic antibiotics alpha pain mrsa dont oa treating icds guideline evidence practice prevention standard primary disregarding manage painkillers ')) * 3+ (MATCH (post_title) AGAINST ('january 2011 morning medicine stories health top ')) * 3,1) as score
from wp_posts
where (post_status IN ( 'publish', 'static' ) and ID != '49994') and post_password ='' and post_date > date_sub(now(), interval 18 month) and post_type = 'page'
group by ID
having score >= 5.00 order by score desc limit 6) on duplicate key update date = now();
I've already made the changes suggested in the FAQ to make the plugin less database intensive. Is there anything else I can do to solve some of these issues? Thanks in advance!
