Hi,
YARPP is the only one of its kind that I've actually gotten to work properly. However I've discovered that it's causing havoc with MsQL. Here's the report from my (excellent) hosting co who brought it to my attention:
Bill
We've been seeing a lot of large, badly written MySQL queries coming in from your user of late. It looks to be something to do with a plugin, "yarpp_related_cache". Would you be able to disable the plugin involved?
I've copied the query report below, if you're interested (or if you want to send it on to the plugin's creator :))
Best regards,
Johnathon
Count : 85
Time : 2899 s total, 34.105882 s avg, 11 s to 119 s max
95% of Time : 2395 s total, 29.9375 s avg, 11 s to 85 s max
Lock Time (s) : 0 total, 0 avg, 0 to 0 max
95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent : 0 avg, 0 to 0 max (0.00%)
Rows examined : 17.88M avg, 4.80M to 65.18M max (62.57%)
Database : creative
Users :
creative@truth-int.positive-internet.com 192.168.254.1
EXPLAIN : Not a SELECT statement
Query abstract:
SET timestamp=N; INSERT INTO wp_yarpp_related_cache
(reference_id,id,score) (SELECT N, id, (N+ (MATCH (post_content) AGAINST
('S')) * N+ (MATCH (post_title) AGAINST ('S')) * N+ COUNT( DISTINCT
tagtax.term_taxonomy_id ) * N+ COUNT( DISTINCT cattax.term_taxonomy_id )
* N) AS score FROM wp_posts LEFT JOIN wp_term_relationships AS thistag
ON (thistag.object_id = N ) 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 =
'S') LEFT JOIN wp_term_relationships AS thiscat ON (thiscat.object_id =
N ) 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 =
'S') WHERE (post_status IN ( 'S', 'S' ) AND id != 'S') AND post_password
='S' GROUP BY id HAVING score >= N.N AND COUNT( DISTINCT
cattax.term_taxonomy_id ) >= N AND COUNT( DISTINCT
tagtax.term_taxonomy_id ) >= N ORDER BY score DESC LIMIT N) UNION
(SELECT N, id, (N+ (MATCH (post_content) AGAINST ('S')) * N+ (MATCH
(post_title) AGAINST ('S')) * N+ COUNT( DISTINCT tagtax.term_taxonomy_id
) * N+ COUNT( DISTINCT cattax.term_taxonomy_id ) * N) AS score FROM
wp_posts LEFT JOIN wp_term_relationships AS thistag ON
(thistag.object_id = N ) 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 =
'S') LEFT JOIN wp_term_relationships AS thiscat ON (thiscat.object_id =
N ) 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 =
'S') WHERE (post_status IN ( 'S', 'S' ) AND id != 'S') AND post_password
='S' GROUP BY id HAVING score >= N.N AND COUNT( DISTINCT
cattax.term_taxonomy_id ) >= N AND COUNT( DISTINCT
tagtax.term_taxonomy_id ) >= N ORDER BY score DESC LIMIT N) ON duplicate
KEY UPDATE date = now();
Query sample:
SET timestamp=1263882775;
insert into wp_yarpp_related_cache (reference_ID,ID,score) (SELECT 300,
ID, (0+ (MATCH (post_content) AGAINST ('percent his inflation growth
eurozone year prices europe britain trichet level recession french bank
economy european billion president may eu ')) * 3+ (MATCH (post_title)
AGAINST ('walker martin unravel economies european ')) * 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 thistag ON (thistag.object_id = 300 )
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 = 300 )
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 != '300') and
post_password =''
group by id
having score >= 2.50 and COUNT( DISTINCT cattax.term_taxonomy_id ) >= 1
and COUNT( DISTINCT tagtax.term_taxonomy_id ) >= 1 order by score desc
limit 10) union (SELECT 300, ID, (0+ (MATCH (post_content) AGAINST
('percent his inflation growth eurozone year prices europe britain
trichet level recession french bank economy european billion president
may eu ')) * 3+ (MATCH (post_title) AGAINST ('walker martin unravel
economies european ')) * 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 thistag ON (thistag.object_id = 300 )
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 = 300 )
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 != '300') and
post_password =''
group by id
having score >= 2.50 and COUNT( DISTINCT cattax.term_taxonomy_id ) >= 1
and COUNT( DISTINCT tagtax.term_taxonomy_id ) >= 1 order by score desc
limit 10) on duplicate key update date = now();
