Support » Plugin: Yet Another Related Posts Plugin (YARPP) » [Plugin: Yet Another Related Posts Plugin] SQL performance

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

Viewing 6 replies - 1 through 6 (of 6 total)
  • 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:
    possible_keys: reference_ID,ID,score
    key: reference_ID

    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',
      KEY reference_ID (reference_ID),
      KEY ID (ID)

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

Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘[Plugin: Yet Another Related Posts Plugin] SQL performance’ is closed to new replies.