How To Fix MySQL Index Conflicts Caused by YARPP Related Posts Plugin
WordPress is a very poplar blogging platform and in some cases it used as a e-commerce site, job portal etc. The platforms extensibility is due to the huge repository of plugins which help extend the functionality of the platform.It is always suggested to limit the number of plugins used in a setup, that is because there is very good chance of database issues which can be caused due to plugins.
For example many a times users try to use different plugins for similar functionality to test which plugin offers more flexibility one such category is the Related Posts plugin which many users try.
In this post I’m going to show how the use of similar plugins cause DB issues.Plugins I used in this study are YARPP and Contextual Related Posts.
I was earlier on a VPS and was using YARPP related posts plugin without any trouble. Later when I moved over to Shared Hosting the hosting provider suggested I not use YARPP as it was taking considerable resources.So I deleted it and tried Contextual Related Posts and then I saw my Database showing that the table wp_posts has crashed.
I initially thought MySQL’s optimize, repair could fix this issue, but that did not solve the problem.Then I looked into the MySQL DB for errors, and then In found issues in the Indexes of the wp_posts table.
Steps To Check for Index issues and resolve them.
Before we proceed, Take a backup of your Database as this involves making changes to important tables of the Database.
How To Check For Index Issues In Your Database
- Open PHPMyAdmin
- Select the Database
- Select wp_posts table and Click on Structure.
- This opens the structure of the wp_posts table. Click on Details to expand the Indexes.
- Any warnings are shown in boxes as can be seen in image below. Warning message after Deleting YARPP and Installing Contextual Related Posts.
- Here indexes of both plugins can be identified by the prefix yarpp_ and crp_ .
How to fix this issue
- “ Don’t just Inactive but Delete the plugins when not in use” .
- Next Goto the indexes of the table and remove the indexes the plugins created by clicking on the cross mark ( circled in the image below ) against the keyname.
- The indexes of both plugins can be identified by the prefix yarpp_ and crp_ .
This solved my issue with regard to frequent crashing of the database.
My Observations :
- I noticed that YARPP does not remove the indexes even after deleting the plugins.
- Contextual Related Posts plugin removed the Indexes after deleting the plugin.
The next question ? How to test plugins before installing on your server.
- Have a localhost setup with similar theme and plugins.
- Test plugins on localhost before installing on the actual server.
YARPP is not recommended on shared hosting by hosting service providers as the related posts queries can be resource intensive.It can be tweaked
The author of the plugin mitcho (Michael Yoshitaka Erlewine) said the below in a support thread regarding YARPP causing high server load
The bottom line, though, is that the YARPP algorithm which gives you those good results comes with a certain computational cost. If that’s something you value, you may want to move up to a higher hosting level or another hosting company.
Source – WordPress Support
What the plugins FAQ’s say on the load issue
Does YARPP slow down my blog/server?
The YARPP calculation of related content does make a little impact, yes. However, YARPP caches all of its results, so any post’s results need only be calculated once. YARPP’s queries have been significantly optimized since version 3.5.
If you are running a large site and need to throttle YARPP’s computation, try the official YARPP Experiments plugin which adds this throttling functionality.
Source – WordPress
You can also run this MySQL Query to drop the yarpp related indexes
ALTER TABLE wp_posts
DROP INDEX yarpp_title,
DROP INDEX yarpp_content;