WordPress Queries Reviewed

WordPress database queries have been put under scanner by Domas Mituzas. Very casually he looks at the possible optimizations in MySQL (database used by WordPress) queries for a single page view. Probably more optimizations are possible when other complicated activities are considered.

Even though a casual look, he makes some very hard-hitting points. Here are the highlights:

There are multiple lessons here:

  • No need to put options in database when you can have it serialized on the disk.
  • No need to put bulk RSS feed data together with lots of small options – use other table for that.
  • Here again, even if putting bulk data together, use indexes for getting proper subset. Anyway, above points are somewhat more important

Anyway, major issues:

  • Do not use DISTINCT or GROUP BY if you do not have to.
  • Use indexes for sorting, row selection and offset retrieval. Create multiple indexes.
  • Avoid putting bulk data together with metadata.
  • Count and save. Reuse.

Even though applied to WordPress, these suggestions would apply to any good database design. One very important point I like is not to put the bulk data together with metadata. The metadata (usually, data about data) can be queried more frequently as compared to the actual data. Logically, the metadata can be used as a factor whether bulk data is required to be retrieved or not. This can save substantial amount of processing time.

As a wishlist I will add that maybe WordPress should upgrade to MySQL 5.0 and use stored procedures.

I think more such reviews are required as they can substantially improve the product, eventually benefiting all of us. Ryan Boren has acknowledged this and has said that some of the suggestions have been implemented and some are being tracked. I think some redesign of the database will be required to optimize it.

Update

Matt has specified in the comments that lot of the suggestions are already implemented in WordPress 2.0.

Having said this, WordPress already has pretty good performance. As written earlier, Sitescore gives good marks to performance which is all because of WordPress. But more enhancements are not harmful :-), considering WordPress is being used in innovative ways all around.

Earlier, I have reviewed WordPress from a Content Management System (CMS) perspective. I have registered one more rant about the user interface for writing.

Finally, I think there is a benefit of open source that we see here. Open source approach makes so much talent available!

Technorati tags: , ,

Copyright Abhijit Nadgouda.

3 Responses to “WordPress Queries Reviewed”

  1. Matt Says:

    Note that his analysis was against 1.5, a lot was already fixed in 2.0. He came by the dev channel and we went through a lot of the optimizations he suggested, some worked and some didn’t, but the ones that did are already in trunk and give a 10-20% improvement in performance. I think Domas is a great example of what’s beautiful about Open Source — the most brilliant people on any given topic can contribute to a project by intelligently and politely voicing their concerns and have the core codebase respond within a week.

  2. donncha’s blog » Links For Friday, April 7, 2006 Says:

    […] Abhijit Nadgouda @ iface » Blog Archive » WordPress Queries Reviewed […]

  3. Abhijit Nadgouda Says:

    Thanks for the clarification Matt, I have updated the post to reflect this.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: