YARPP 3.2.1b2 separates out some of the keys on the related cache. Could you try it out and see if that improves your situation?
http://downloads.wp.xz.cn/plugin/yet-another-related-posts-plugin.3.2.1b2.zip
Thread Starter
timbrd
(@timbrd)
Will be the same at least at
cache-tables.php line 94 (JOIN will force full scan)
cache-tables.php line 221
I would leave Primary key as it is, drop index by Score and add index by ID.
where_filter from cache-tables.php (line 103) is supposed to use Score index but MySQL ignores it:
possible_keys: reference_ID,ID,score
key: reference_ID
Thread Starter
timbrd
(@timbrd)
Correction, in my case PRIMARY KEY ( reference_ID , ID ) will not work:
ALTER TABLE <code>wp_9_yarpp_related_cache</code> ADD PRIMARY KEY ( <code>reference_ID</code> , <code>ID</code> ) ;
Duplicate entry '43334-28576' for key 'PRIMARY'
I’m using now the following:
CREATE TABLE IF NOT EXISTS wp_9_yarpp_related_cache (
reference_ID bigint(20) unsigned NOT NULL DEFAULT '0',
ID bigint(20) unsigned NOT NULL DEFAULT '0',
score float unsigned NOT NULL DEFAULT '0',
date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY reference_ID (reference_ID),
KEY ID (ID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
@timbrd YARPP 3.2.1b2 has code to actually clear the table before adding the new primary key of (reference_ID, ID).
If you keep the reference_ID as your only primary key, it will totally mess up the logic… i.e., you will only get one related posts per post.
Thread Starter
timbrd
(@timbrd)
Oh, I see it, to avoid duplicates. I’ve change it to
PRIMARY KEY ('reference_ID','ID'),
KEY 'ID' ('ID')
Okay, I just fixed this in 3.2.1b4, though keeping the score index as well, just in case.
http://downloads.wp.xz.cn/plugin/yet-another-related-posts-plugin.3.2.1b4.zip
Because you’ve manually changed your table, most likely you’ll want to drop the wp_yarpp_related_cache table once, and YARPP will rebuild it with the new schema.
