William Bowles

What about the author? It’s a shame as it’s the only of its kind that used to actually work, at least on my setup and as my site is a news and current affairs ‘blog’ being able to cross-reference articles is a tremendously useful to readers.

Bill

Hi,
YARPP was 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();

http://www.creative-i.info

why not use Vodpod?

Not only does it add the videos, it also creates a separate library (viewable onsite) and if you have Facebook/Myspace account, it’ll add them directly to it as well.

See mine at http://www.creative-i.info

Bill

Solution? Switch to an alternate plugin, that’s what I’ve done, it’s called Twitme <http://www.phpvrouwen.nl/twitme/&gt;. I’ve had it with Twitter Tools, it’s more trouble than it’s worth and the switch to the bit.ly just seems to add another layer of complexity as it means I’ve got to add yet another plugin! All this does it add even more overhead.

Bill

<http://twitter.com/Creative_i&gt;

PS: Deselect Twitter Tools before installing Twitme.