How many database calls does it take to change a website?

Originally posted at http://endoflevelboss.org/2005/12/01/how-many-database-calls-does-a-website-need on Thu, 01 Dec 2005

Websites are fairly static things. You might notice that there's a little note at the bottom of every page on this site telling you how many queries it took to build. Wordpress generally requires quite a few more queries than I'd like, but it's not a lot higher than your average CMS. Every time someone requests my front page (with an HTTP GET) Wordpress has to perform 11 queries. I could reduce this number if I altered some of the code in the template. For example, the list of links is taken from a table in the database and displayed by Wordpress in the way you see it. So that's 1 query that I could remove just by having the links pre-written in HTML in the EndOfLevelBoss design.

1 post = 1 insert

But even if I managed to reduce the number of queries so that there was just 1 query for every GET request, is that good enough? When I post a new entry, I submit a form which sends an HTTP POST request to the site, and then if everything looks good Wordpress updates the database with the new entry using an INSERT. Then when someone comes along to view the site, they send a GET request to Wordpress, which does a SELECT on the database to get the latest entries and then sends the page to the reader. The problem here is that often a number of different readers will request the same URL at around the same time. If 8 readers want to look at the same page at the same time, Wordpress does 8 SELECTS; 1 for every GET.

8 gets = 8 selects

This usually won't pose a problem, but what if 1,000 readers request the same URL at the same time? Does it really make sense to perform 1,000 queries when they're all going to contain the same information anyway? Obviously, it doesn't. While some information might need to be updated regularly, such as how many comments an entry has, etc., most information doesn't change regularly. Once an entry is written it's unlikely to be updated.

Many CMSs make use of caching to avoid unnecessary communication with the database. MediaWiki, for example, only updates the cached version of an article when the article is edited. There are plugins that allow caching for Wordpress, but I have had mixed success in using them.

The Wordpress team have recently announced details of Wordpress 2.0, and one of the features included is "Persistent Object Cache". This feature caches objects returned from the database, meaning that if 8 users request the same URL, Wordpress only really needs to carry out the query to get that entry once! Even though it probably won't make a difference to most blogs ( Ryan reports a difference of only about 1 or 2 percent) it will make a huge difference to sites that receive a lot of traffic or run Multi-User Wordpress.