WordPress.org

1. timbrd
   Member
   Posted 1 year ago #

   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.

2. mitcho (Michael Yoshitaka Erlewine)
   Member
   Posted 1 year ago #

   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.wordpress.org/plugin/yet-another-related-posts-plugin.3.2.1b2.zip

3. timbrd
   Member
   Posted 1 year ago #

   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

4. timbrd
   Member
   Posted 1 year ago #

   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;

5. mitcho (Michael Yoshitaka Erlewine)
   Member
   Posted 1 year ago #

   @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.

6. timbrd
   Member
   Posted 1 year ago #

   Oh, I see it, to avoid duplicates. I've change it to
   

   PRIMARY KEY ('reference_ID','ID'),
     KEY 'ID' ('ID')

7. mitcho (Michael Yoshitaka Erlewine)
   Member
   Posted 1 year ago #

   Okay, I just fixed this in 3.2.1b4, though keeping the score index as well, just in case.

   http://downloads.wordpress.org/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.

Topic Closed

This topic has been closed to new replies.