WordPress Performance and Permalinks – quick tip

11 July 2011 comment icon7 | Categories: performance

Whoa it’s been nearly two weeks since I’ve posted on the blog. I’m up the walls busy at the moment working on developing some new WordPress themes for clients and our first public theme which should see the light of day before the middle of August. More on that a bit later in the month. We’ve also not forgotten about the High Performance WordPress post series – it’s just having to take a back seat for the moment – I promise to push out a few more on the series before the end of July! Anyway, last week we ran into some major performance problems with a clients WordPress powered website that I had to squeeze out a quick post to let you good folks know about the issue just in case it rears it’s ugly head.

Permalinks.

Wonderful little inventions that let us have nice clean search engine friendly urls. From a backend UI perspective, the WordPress permalink implementation is excellent. But, once you start to have a lot of posts and pages on your site you WILL hit performance issues if you simply use /%postname%/ as your preferred permalink structure. The website in question had over 10,000 records in the posts table. Now the interesting thing was that there was nowhere near that many posts/pages actually on the website – more like a couple of hundred blog posts and about 1,400 pages. The balance of records in the posts table were actually WordPress revisions. There’s a lot of updates happening on the site at the moment and the posts table had gotten out of control. Never fear, this little simple query cleared out any old revisions from the posts table:

NOTE: PLEASE PLEASE PLEASE BACKUP YOUR DB BEFORE RUNNING THIS QUERY! DON’T SAY YOU WERE NOT WARNED!!

[codesyntax lang=”sql”]

DELETE a,b,c
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision'

[/codesyntax]

Credit to Lester for this nifty sql statement

As long as you maintain regular backups of your DB I see no reason to retain all your posts revisions on a long term basis. Some people might – I don’t. If performance is important to you (and it should be) then I advise keeping your posts table nice and lean by cleaning it up every few months. There’s also a nice little plugin for this if you’re that way inclined.

To keep the maintenance of your posts table low, you can also switch off revisions completely if they are overkill for your site. Just add the following to your wp-config.php:

[codesyntax lang=”php”]

define('WP_POST_REVISIONS', false );

[/codesyntax]

So why is this important?

So why are the number of records in your posts table important to your permalinks and performance? In a nutshell, the more records in your posts table, the longer WordPress takes to execute key queries to retrieve the data required to load every page and post on your site. If your site will only ever have 40-50 posts/pages you can probably stop reading now. Otherwise, you will want to read Otto’s very well explained post on why certain permalink structures are more harmful than others. Seriously read it now. No time to read it? Ok, basically WordPress keeps it’s rewrite rules nice, simple and elegant by using cascading specificity – a bit like CSS. When your permalink structure is something very general like /%postname%/ you are in effect overriding this cascade and WordPress handles it by switching to use more verbose page rewriting rules. The impact of this cannot be overstated on large WordPress websites. On a site with 1,000 pages, it basically kills a VPS server with 1GB of ram and a trickle of traffic. Get a sudden burst of traffic and you’re dead – that’s even with something like W3 Total Cache running. When I switched the permalink structure to something more specific like /%year%/%postname%/ (which was my preferred structure after lots of testing – and is Otto’s preferred structure too.) the impact was immediate. CPU and RAM usage fell back to what I would expect for a site like this. Switching back to /%postname%/ just to confirm this was definitely the problem took nearly 10 mins for WordPress to process the request – and an immediate CPU/RAM spike – not good.

I can’t believe I’ve never come across this before – it’s actually noted in the WordPress codex that starting a permalink structure with /%postname%/ is a bad idea from a performance perspective – it’s just kinda buried. Given that so many SEO’s stress the importance of using this exact permalink structure I think that WordPress should shout a bit more about this because it’s probably giving some people a very bad impression of WordPress performance for no reason. The really interesting thing is that this doesn’t impact on the permalink structure for pages which I was worried about from an SEO perspective. I thought I’d have to setup a crapload of 301 redirects for pages and I didn’t fancy telling the website owner that all their pages now had a year in the permalink structure. Thankfully the problem never manifested itself as pages don’t seem to be affected by the permalink rules you control via the WordPress backend. Sp, while the /%postname%/ structure will kill your pages load time, the rule is just for blog posts. Pages stay nice and friendly in the format “www.mydomain.com/page-title” In any event, I find that having a date in the permalink for blog posts is a really big help when trying to verify how useful any given page will be. I don’t do that here yet but will be switching this in the near future once I knock a few items off the existing todo list.

7 Responses to “WordPress Performance and Permalinks – quick tip”

  1. Joost de Valk on 11 July 2011 at 9:38 pm said: Permalink

    Chris Coyier over at CSS Tricks recently had this issue and blogged about it. He’s got a ridiculous amount of pages & pageviews and he’s the first ever i’ve seen having problems with it. Also, on that post, Nacin commented that this’ll be fixed in 3.3, so basically, I would stick with %postname% 🙂

    • Ed on 11 July 2011 at 9:54 pm said: Permalink

      Hey Joost – thanks for adding your input!

      I missed Chris’s post. Great to hear that Nacin and co. are going to sort this in 3.3.

      Guess you can indeed stick with %postname% if that’s the case 🙂

      Ed

  2. Igor Mateski on 19 August 2011 at 5:15 pm said: Permalink

    Just read about this performance issue. My sites are very small, a few dozen pages each, so I guess they aren’t too big of an issue for the shared hosting server that runs my sites. I use Yoast’s SEO plugin and the category/post setting for URLs, and from SEO perspective things run just fine.
    I found this http://core.trac.wordpress.org/changeset/18541/ ad uploaded the edited files on my sites but didn’t really notice any big difference, perhaps they’re too small to get any difference.

    • Ed on 23 August 2011 at 10:26 pm said: Permalink

      Hey Igor, yeah it’s only really when you start hitting 200-300+ pages/posts where this issue starts to become a big problem – I’m basing that on my own personal experiences.

  3. Pittayut on 10 January 2012 at 7:35 am said: Permalink

    Hello Ed ^_^
    I complete read your post , but when I see Joost de Valk Comment I very confuse .
    Now I can use /%postname%/ for my wordpress site permalink without problem about performance (now my site is wordpress 3.3.1 vertion) ?

    • Ed on 11 January 2012 at 10:23 pm said: Permalink

      Hey Pittayut – this was an issue back in July ’11 but with 3.3+ it’s no longer an issue – thankfully!

      Stick with /%postname%/

      Ed

  4. Asa on 19 December 2012 at 4:27 am said: Permalink

    Stephan I’ve heard that too about static pages. Was thniikng of doing a post on it. I’ve looked into it a little hoping to find some quick evidence. Haven’t found anything solid yet, but I haven’t looked into deeply. Have you seen anyone with some credible evidence?Joe

Leave a Reply