Performance can be increased by correct indexes in table yarpp_related_cache. The table has a multicolumn index created as “PRIMARY KEY ( score , date , reference_ID , ID )”. MySQL cannot use such index in most of SELECTs, example:
# Query_time: 0.017838 Lock_time: 0.000034 Rows_sent: 0 Rows_examined: 100543 select distinct reference_ID from wp_yarpp_related_cache where ID = 43287;
Single column indexes ‘reference_ID’ and ‘ID’ are necessary for such requests.
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?
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:
Correction, in my case PRIMARY KEY (
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.
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.
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.
- The topic ‘[Plugin: Yet Another Related Posts Plugin] SQL performance’ is closed to new replies.