SP Blogger

Make Money Online, Blogging Tips, SEO, Investment Guide, Financial Planning…

How to optimize WordPress and clean database? The easiest way, download WordPress WP-Optimize Plugin, activate and use it. The end… Nope, that’s not the end. Yes, WP-Optimize is a database cleanup and optimization tool. It doesn’t require PhpMyAdmin to optimize your database tables. And it allows you to remove post revisions, comments in the spam queue, un-approved comments within few clicks. But by using it is not enough, thanks to AllGuru.NET guide, it explains the detail to further delete orphan records in wp_term_relationships table.

DELETE FROM wp_term_relationships
WHERE NOT EXISTS (
SELECT * FROM wp_posts
WHERE wp_term_relationships.object_id = wp_posts.ID);

Note – do make a backup of your database before start executes any of WP-Optimize or manual delete tasks.

Above scripts are useful for those disabled the post revision function and want to clean up the orphan records that was not deleted by WP-Optimize. By the way, you can continue to clean wp_options table with WordPress Clean Options Plugin. But be warned, this plugin over cleaned my wp_options table. So use it carefully.

HostMonster and Bluehost list out all the slow SQL queries that use more than 1 second query_time in $HOME/tmp/mysql_slow_queries folder. I guess they have poor performance DB server because the other 2 web hosting that I use ~ HostGator and Just Host don’t have it. Anyway, if you see lot of log files there, this mean that you are using quite some CPU resources and getting sooner to get suspended like I did. When you look into these logs, you will find most of them are WordPress standard SQL queries and you can’t do any optimization on the queries themselves. But there are other ways to reduce access to DB server like wp-config.php tweaks.

How to speed up WordPress blog with wp-config.php tweaks?

By adding WP_HOME and WP_SITERUL, WordPress does not need to access to DB server to grab these 2 values every time when header or footer call these for RSS, CSS, JavaScript location on your theme.

define(‘WP_HOME’, ‘http://spblogger.com’);
define(‘WP_SITEURL’, ‘http://spblogger.com’);

WordPress Security Keys. Have it pre-defined in wp-config.php to eliminate WordPress to generate there salts for you if none are provided. Get yours here.

define(‘AUTH_KEY’, ‘xxx’);
define(‘SECURE_AUTH_KEY’, ‘xxx’);
define(‘LOGGED_IN_KEY’, ‘xxx’);
define(‘NONCE_KEY’, ‘xxx’);

Disable post revision function. This will reduce number of rows in WordPress post table == faster page finding and loading. I don’t use it anyway, so I disabled it.

define(‘WP_POST_REVISIONS’, false);

Be sure to check out AllGure.NET post for more info.

As I said, HostMonster and Bluehost are famous with CPU Throttling. Therefore, a WordPress cache plugin is a must installed on their hosting. My account got suspended even I am using WP Super Cache for quite some time. After do a comparison between WP Super Cache and W3 Total Cache, I decided to use W3 Total Cache as it is better than WP Super Cache.


HostMonster CPU Limiting Factors (Throttling)

I am using W3 Total Cache v0.9.1.3 on WordPress v3.0.4. W3 Total Cache is working out of the box with default settings. However, I discovered HTTP (gzip) compression is not working on HostMonster and Just Host most of the time. Therefore, CSS, JavaScript and HTML are not compressed with gzip. Only HostGator works perfectly. Must be web hosting issue.

On the other hand, W3 Total Cache failed to cache other object like images with expires header, cache control header and entity tag (eTag). None of the 3 web hosting that I use is working. So no “Instant” second page views (browser caching after first page view). Besides, its minify function might broke your other plugin’s CSS and JavaScript. I tried on PrettyGirl2u.com and it failed totally. So use it carefully.

By the way, I enabled database caching via disk. It does reduce some CPU resources on HostMonster. But it can’t help much if your visitors or yourself is ‘touching’ DB server directly by posting comments or editing posts. As you can see picture above on the last 1 hour, CPU usage spiked because I was replying few comments.

Overall, W3 Total Cache is a must installed plugin on WordPress blog. Not perfect but worth to install. And do tweak it until you get the best performance out of it.

Download W3 Total Cache WordPress Plugin here.

HostMonster is famous with its CPU Throttling. You can’t use too much CPU resources no matter in HTTP, FTP, database… They will be counted and not to over the limit else your web hosting account will be suspended like I did.

WP Super Cache is not a stranger to me. I had been using it for 1 year plus. And it served me well. W3 Total Cache on the other hand, it is new to me. But it has more features when compare to WP Super Cache. However, not all the features are working (at least to me). And some crashes with other plugin which use own CSS.


CPU usage with WP Super Cache


CPU usage with W3 Total Cache

Based on benchmark, W3 Total Cache makes my website use less CPU resources. Must be its database caching system’s help. Therefore, it stays on my blogs. However, make sure that you try to play around the all its features until you get the best performance of your website.

LinkWithin was the previous WordPress related posts with thumbnails plugin that I used long time ago. It is working great but it uses its own server to manage those related posts. And causing Google Analytics data with widget.linkwithin.com as referrer. I don’t like that as it might increase bounce rate. Therefore, I searched and found a self-hosted WordPress Related Posts Thumbnails Plugin for PrettyGirl2u.com.


WordPress Related Posts Thumbnails Plugin

WordPress related posts with thumbnails plugin is good for those blog with lot of pictures. Else I would recommend to use text based WordPress Related Post Plugin. So choose wisely…

Download WordPress Related Posts Thumbnails Plugin here.


Page 1 of 3123



Email Updates

Enter your Email to subscribe:


Subscribe to SP Blogger feeds