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.
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.
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.
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.