WordPress.org

Ready to get started?Download WordPress

Forums

Yet Another Related Posts Plugin (YARPP)
[Plugin: Yet Another Related Posts Plugin] SQL performance (7 posts)

  1. timbrd
    Member
    Posted 3 years 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
    Plugin Author

    Posted 3 years 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 3 years 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 3 years 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
    Plugin Author

    Posted 3 years 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 3 years 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
    Plugin Author

    Posted 3 years 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.

About this Plugin

About this Topic