[Plugin: Yet Another Related Posts Plugin] YARPP 2.0.5 Problems

Yes, there was a query bug in 2.0.5 that I’m in the process of resolving right now… but it doesn’t create any temporary tables. Are you sure that was due to YARPP??

If you send me a little more about your set up or the errors you’re getting, I’d appreciate it.

mitcho

I have a problem with 2.0.5, using previous version no issues.
This is what appears at top of an article:
WordPress database error: [Unknown column ‘wp_posts.ID’ in ‘on clause’]
SELECT ID, post_title, post_date, post_content, (MATCH (post_content) AGAINST [….]

vfvalent is showing the exact same problem. Here’s output from mysqladmin on one of our domains jlbreport.com.

[root@beasleyallen1 jlbreport]# mysqladmin processlist
+-------+-----------+-----------+-----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id    | User      | Host      | db        | Command | Time | State                | Info                                                                                                 |
+-------+-----------+-----------+-----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 23329 | jlbreport | localhost | jlbreport | Query   | 7    | Copying to tmp table | SELECT ID, post_title, post_date, post_content, (MATCH (post_content) AGAINST ('payday loans loan in |
| 23335 | jlbreport | localhost | jlbreport | Sleep   | 0    |                      |                                                                                                      |
| 23337 | root      | localhost |           | Query   | 0    |                      | show processlist                                                                                     |
+-------+-----------+-----------+-----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
[root@beasleyallen1 jlbreport]# mysqladmin processlist --verbose
+-------+-----------+-----------+-----------+---------+------+----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id    | User      | Host      | db        | Command | Time | State                | Info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+-------+-----------+-----------+-----------+---------+------+----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 23329 | jlbreport | localhost | jlbreport | Query   | 10   | Copying to tmp table | SELECT ID, post_title, post_date, post_content, (MATCH (post_content) AGAINST ('payday loans loan interest lending ohio state lenders capped people states % rate ? financial 36% industry close cap cost')) as bodyscore, (MATCH (post_title) AGAINST ('ohio lenders payday crackdown')) as titlescore, COUNT( DISTINCT tagtax.term_taxonomy_id ) AS tagscore, COUNT( DISTINCT cattax.term_taxonomy_id ) AS catscore, ((MATCH (post_content) AGAINST ('payday loans loan interest lending ohio state lenders capped people states % rate ? financial 36% industry close cap cost')) * 1 + (MATCH (post_title) AGAINST ('ohio lenders payday crackdown')) * 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 = 499 )
	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 = 499 )
	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' ) && ID != '499')  and post_password ='' 			and post_type IN ('post') GROUP BY id  having  score >= 5 order by score DESC limit 5 |
| 23338 | root      | localhost |           | Query   | 0    |                      | show full processlist                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+-------+-----------+-----------+-----------+---------+------+----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

No, @vfvalent’s issue is a different one–it’s a boneheaded bug I introduced in 2.0.5. @vfvalent, if you email me I’ll send you a development version which fixes that bug. My apologize. It will be released soon as 2.0.6, in case you don’t mind waiting a couple days.

@wohlfordba – I see… it’s just copying to a temporary table as one of the join gets too big, not because I use a create temporary. Could you try running this query?

explain SELECT ID, post_title, post_date, post_content, (MATCH (post_content) AGAINST ('payday loans loan interest lending ohio state lenders capped people states % rate ? financial 36% industry close cap cost')) as bodyscore, (MATCH (post_title) AGAINST ('ohio lenders payday crackdown')) as titlescore, COUNT( DISTINCT tagtax.term_taxonomy_id ) AS tagscore, COUNT( DISTINCT cattax.term_taxonomy_id ) AS catscore, ((MATCH (post_content) AGAINST ('payday loans loan interest lending ohio state lenders capped people states % rate ? financial 36% industry close cap cost')) * 1 + (MATCH (post_title) AGAINST ('ohio lenders payday crackdown')) * 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 = 499 )
	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 = 499 )
	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' ) && ID != '499')  and post_password ='' 			and post_type IN ('post') GROUP BY id  having  score >= 5 order by score DESC limit 5

This will give me some information on why this query is slow and how large the intermediate joins are.

Thanks!
mitcho

It works for me. Thanks

@mitchoyoshitaka output follows:

mysql> explain SELECT ID, post_title, post_date, post_content, (MATCH (post_content) AGAINST ('payday loans loan interest lending ohio state lenders capped people states % rate ? financial 36% industry close cap cost')) as bodyscore, (MATCH (post_title) AGAINST ('ohio lenders payday crackdown')) as titlescore, COUNT( DISTINCT tagtax.term_taxonomy_id ) AS tagscore, COUNT( DISTINCT cattax.term_taxonomy_id ) AS catscore, ((MATCH (post_content) AGAINST ('payday loans loan interest lending ohio state lenders capped people states % rate ? financial 36% industry close cap cost')) * 1 + (MATCH (post_title) AGAINST ('ohio lenders payday crackdown')) * 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 = 499 ) 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 = 499 ) 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' ) && ID != '499')  and post_password =''    and post_type IN ('post') GROUP BY id  having  score >= 5 order by score DESC limit 5;
+----+-------------+----------+--------+--------------------------------------+------------------+---------+----------------------------------------------------------+------+----------------------------------------------+
| id | select_type | table    | type   | possible_keys                        | key              | key_len | ref                                                      | rows | Extra                                        |
+----+-------------+----------+--------+--------------------------------------+------------------+---------+----------------------------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | wp_posts | ref    | PRIMARY,post_status,type_status_date | type_status_date |      20 | const                                                    |  329 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | thistag  | ref    | PRIMARY                              | PRIMARY          |       8 | const                                                    |  176 | Using where; Using index                     |
|  1 | SIMPLE      | tagrel   | eq_ref | PRIMARY,term_taxonomy_id             | PRIMARY          |      16 | jlbreport.wp_posts.ID,jlbreport.thistag.term_taxonomy_id |    1 | Using index                                  |
|  1 | SIMPLE      | tagtax   | eq_ref | PRIMARY                              | PRIMARY          |       8 | jlbreport.tagrel.term_taxonomy_id                        |    1 |                                              |
|  1 | SIMPLE      | thiscat  | ref    | PRIMARY                              | PRIMARY          |       8 | const                                                    |  176 | Using where; Using index                     |
|  1 | SIMPLE      | catrel   | eq_ref | PRIMARY,term_taxonomy_id             | PRIMARY          |      16 | jlbreport.wp_posts.ID,jlbreport.thiscat.term_taxonomy_id |    1 | Using index                                  |
|  1 | SIMPLE      | cattax   | eq_ref | PRIMARY                              | PRIMARY          |       8 | jlbreport.catrel.term_taxonomy_id                        |    1 |                                              |
+----+-------------+----------+--------+--------------------------------------+------------------+---------+----------------------------------------------------------+------+----------------------------------------------+
7 rows in set (0.00 sec)

mysql>

@mitchoyoshitaka P.S. I’m out til Tuesday. Will try to email you.

@wohlfordba – Hm… that query doesn’t look like it should take long, and it looks pretty well optimized. Let me know if you continue to feel the queries are taking too long.

mitcho

I have like 8k posts on my blog, 20k unique visitors a day, I just don’t know what happened because it died, it’s showing me “Error Connecting to Database” error messages. I just found out that it was the YARPP 2.0.5 update, I had to temporarily deactivate the plugin until it gets fix. I love this plugin. Thanks

@unseenmortal I’d suggest using the 2.0.4 version. This one has issues.

@unseenmortal – Please try the new 2.0.6 version, as WP may have interpreted the bug in 2.0.5 as a connection error. Otherwise, I would like to know more about your database set up in order to help resolve the problem. If you could tell me what version of MySQL you are running, I would appreciate that… in addition if you email me, I can get you a debug version that will help better identify what that “connection error” seems to be.

Sorry for the troubles–
mitcho