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!
Copyright Abhijit Nadgouda.
April 7th, 2006 at 11:58 pm
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.
April 8th, 2006 at 4:00 am
[...] Abhijit Nadgouda @ iface » Blog Archive » Wordpress Queries Reviewed [...]
April 14th, 2006 at 5:22 pm
Thanks for the clarification Matt, I have updated the post to reflect this.
January 23rd, 2007 at 9:31 am
[...] Wordpress 2.1, named Ella, is released. The version 2.1 has a lot of features that are included in the current Wordpress.com platform, like inherent ability to set any page as homepage, more AJAXed administration interface and a better editor. It also includes enhanced database queries that Domas Mituzas had reviewed, and overall a cleaner code. Overall, it indicates towards a new Wordpress generation. I tried out on my local machine and it looks cool. The first step for me is to test compatibility of plugins and then embrace it. [...]