Recent Posts

Speed up Wordpress by Trimming Your Bloated Database

Posted: September 12, 2008

<new>I’m slowly cleaning up old drafts. This is one from 5 months ago before the redesign (and I’ve not reintegrated Google search yet). However, for those wordpress users - it’s a worthwhile read.</new>

I love analytics. No where else on the web can you get lost in the purity of statistics.

However, there’s a dark side to analytics - storage space.

Admittedly, I don’t take good care of this blog. However, when I went to back up my database today it was 950 mb. Yes, that’s correct, the database was nearing a one gig in size! It wasn’t spam, it wasn’t the huge amount of content on the site, it wasn’t hacked - it was wordpress analytics programs storing stats in the database.

While it’s fun to have access to every possible analytic stat, there’s also something to be said about the speed of your blog (not to mention one should be more concerned about actionable data).

Using phpMyAdmin I removed shortstat, slimstat, and counterize II tables from my database which brought it down to a much more reasonable 50mb; which still seemed large to me. So, after some more rummaging around the database, it seems that Search Meter (a nice plug-in that saves all the searches conducted on your blog) was taking up the last unnecessary 20+ mb in the database.

It was a tough decision to actually put an ad back on the blog. But, the best alternate to the wordpress search was to add a Google custom search. I haven’t figured out the correct layout for the search yet as on some browsers part of the search box is obscured which I’m sure is against the Google TOS - but I’ve not figured out an OK looking CSS yet. However, searches on this site will now have some ads from Google on them. However, I can now get search stats in my AdSense reporting. Unfortunately, that’s not integrated with any other analytics programs yet. I’m waiting for the day Google’s CSE is integrated into Google analytics. That seems the next logical step. One of the best places for keyword research is search results on your own site.

So, after removing those plug-ins, I made sure that both Microsoft Analytics (review here) and Google Analytics (and soon IndexTools by Yahoo) are installed in the blog for full stats. (More free stat programs found via SEMMYs.)

When installing these scripts, please put them in the footer tag (or just above the </body> tag) so that your content loads immediately. It’s ok if you don’t track all your visitors because the tracking script didn’t load. It’s not ok to have your content not load because some tracking script is taking too long to load.

The accomplishment? This blog is loading about 70% faster than previously. A leaner database means a faster site.

P.S. Please use absolute URls for your Favicons. If you use relative paths, the icons will not show up on all pages of your blog.

Share and Enjoy:

Related Information:

« What does the AdWords Change to the ‘Top Position’ Formula mean to you?

San Francisco Seminar Special Late Sign-up Bonus »

Written by Brad Geddes aka eWhisper · Filed Under Blog, Blogging

Comments

2 Responses to “Speed up Wordpress by Trimming Your Bloated Database”

Alex Sysoef on September 12th, 2008 11:06 am

Great tips Brad,

I have wrote several times on how bloated DB can negatively impact your blog and I have discovered those through the pain, just like you did.

Can I make one suggestion? Check your wp_options table (replace wp_ for whatever db prefix you used during install). I have found that in rare ocasions WordPress Dev blog RSS and WordPress News RSS creates enormous amount of entries that look like this:

rss_a5420c83891a9c88ad2a4f04584a5efc
rss_a5420c83891a9c88ad2a4f04584a5efc_ts

Entries like those been known to NOT make your wp_options table grow fast! Deleting those made huge difference on my blog. And yes, it is safe. I haven’t seen this as issue lately but before - it was big problem for me.

Alex
Alex Sysoef on September 12th, 2008 11:07 am

Oope, mistake:

Replace “to NOT make ” with “to make” sorry for multiple posts. Want to be sure info is accurate