Title: [Plugin: Yet Another Related Posts Plugin] SQL performance
Last modified: August 19, 2016
---
# [Plugin: Yet Another Related Posts Plugin] SQL performance
* [timbrd](https://wordpress.org/support/users/timbrd/)
* (@timbrd)
* [15 years, 4 months ago](https://wordpress.org/support/topic/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)
* [mitcho (Michael Yoshitaka Erlewine)](https://wordpress.org/support/users/mitchoyoshitaka/)
* (@mitchoyoshitaka)
* [15 years, 4 months ago](https://wordpress.org/support/topic/plugin-yet-another-related-posts-plugin-sql-performance/#post-1927823)
* 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](http://downloads.wordpress.org/plugin/yet-another-related-posts-plugin.3.2.1b2.zip)
* Thread Starter [timbrd](https://wordpress.org/support/users/timbrd/)
* (@timbrd)
* [15 years, 4 months ago](https://wordpress.org/support/topic/plugin-yet-another-related-posts-plugin-sql-performance/#post-1927832)
* 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](https://wordpress.org/support/users/timbrd/)
* (@timbrd)
* [15 years, 4 months ago](https://wordpress.org/support/topic/plugin-yet-another-related-posts-plugin-sql-performance/#post-1927833)
* Correction, in my case PRIMARY KEY ( `reference_ID` , `ID` ) will not work:
* ```
ALTER TABLE wp_9_yarpp_related_cache ADD PRIMARY KEY ( reference_ID , ID ) ;
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;
```
* [mitcho (Michael Yoshitaka Erlewine)](https://wordpress.org/support/users/mitchoyoshitaka/)
* (@mitchoyoshitaka)
* [15 years, 4 months ago](https://wordpress.org/support/topic/plugin-yet-another-related-posts-plugin-sql-performance/#post-1927837)
* [@timbrd](https://wordpress.org/support/users/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](https://wordpress.org/support/users/timbrd/)
* (@timbrd)
* [15 years, 4 months ago](https://wordpress.org/support/topic/plugin-yet-another-related-posts-plugin-sql-performance/#post-1927838)
* Oh, I see it, to avoid duplicates. I’ve change it to
* ```
PRIMARY KEY ('reference_ID','ID'),
KEY 'ID' ('ID')
```
* [mitcho (Michael Yoshitaka Erlewine)](https://wordpress.org/support/users/mitchoyoshitaka/)
* (@mitchoyoshitaka)
* [15 years, 4 months ago](https://wordpress.org/support/topic/plugin-yet-another-related-posts-plugin-sql-performance/#post-1927897)
* 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](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.
Viewing 6 replies - 1 through 6 (of 6 total)
The topic ‘[Plugin: Yet Another Related Posts Plugin] SQL performance’ is closed
to new replies.
* 
* [YARPP - Yet Another Related Posts Plugin](https://wordpress.org/plugins/yet-another-related-posts-plugin/)
* [Frequently Asked Questions](https://wordpress.org/plugins/yet-another-related-posts-plugin/#faq)
* [Support Threads](https://wordpress.org/support/plugin/yet-another-related-posts-plugin/)
* [Active Topics](https://wordpress.org/support/plugin/yet-another-related-posts-plugin/active/)
* [Unresolved Topics](https://wordpress.org/support/plugin/yet-another-related-posts-plugin/unresolved/)
* [Reviews](https://wordpress.org/support/plugin/yet-another-related-posts-plugin/reviews/)
* 6 replies
* 2 participants
* Last reply from: [mitcho (Michael Yoshitaka Erlewine)](https://wordpress.org/support/users/mitchoyoshitaka/)
* Last activity: [15 years, 4 months ago](https://wordpress.org/support/topic/plugin-yet-another-related-posts-plugin-sql-performance/#post-1927897)
* Status: not resolved