{"id":41607,"date":"2018-01-31T06:00:37","date_gmt":"2018-01-31T11:00:37","guid":{"rendered":"http:\/\/www.shamusyoung.com\/twentysidedtale\/?p=41607"},"modified":"2018-01-30T07:24:39","modified_gmt":"2018-01-30T12:24:39","slug":"database-abuse","status":"publish","type":"post","link":"https:\/\/www.shamusyoung.com\/twentysidedtale\/?p=41607","title":{"rendered":"Database Abuse"},"content":{"rendered":"

The conventional wisdom is that WordPress is really database intensive. I’ve never seen an official critique of it or a proper comparison between WordPress and some other CMSContent Management System<\/span> or publishing platform. This is just the sort of thing I see dismissive blowhards say on forums or StackOverflow. “What did you expect? WordPress puts a heavy load on a mySQL server.” I never really questioned it myself.<\/p>\n

But last week got me thinking about it again when I tried to move this website to DreamHost<\/a>. Is WordPress really that bad? How many dB queries does it take to create a single instance of the front page? How does that compare to other platforms?<\/p>\n

<\/p>\n

Some Background<\/h3>\n

Here is a look at the dB for this site. We're over 400,000 comments these days. Crazy.<\/div>
Here is a look at the dB for this site. We're over 400,000 comments these days. Crazy.<\/div><\/p>\n

Just in case all of this jargon is opaque to you, here’s a quick explanation:<\/p>\n

You’ve noticed that WordPress is a dynamic system. It’s not a bunch of fixed, static pages. The content of the pages changes constantly. When someone leaves a comment, you see it right awayAssuming my spam filter doesn’t decide to harass them for no reason.<\/span>. On the front page, the number of comments is always up-to-date.<\/p>\n

This is accomplished by lashing together two different bits of technology: PHP and mySQL.<\/p>\n

PHP is a “Server-side scripting language”. It is also a hilariously dysfunctional language<\/a>. I understand why it became popular: It solved an important problem, which was the need for dynamic content on the web. What I don’t understand is why – despite its abominable reputation as a nest of confusing syntax, security concerns, undocumented behavior, misleading function names, and alarming bugs – it hasn’t been replaced in the 23 years since it was introduced. Microsoft took a crack at it with their Active Server Pages<\/a>, but the world runs mostly on Linux servers and Linux admins are not eager to adopt proprietary Microsoft software.<\/p>\n

Anyway.<\/p>\n

When you visit this site, your web browser requests a document called index.php, which is a PHP script. The server opens up the script and runs it. The script spits out some HTML, which the server then sends to you.<\/p>\n

mySQL is a database. It holds information in tables. In the case of this blog, there’s a table with a list of every post on the site. There’s another table of every comment, along with what posts they belong to. Then we have a table that keeps track of all the categories. Another for users. And so on.<\/p>\n

So when the WordPress PHP script runs, it will talk to the mySQL database and ask for information. Stuff like, “Hey, I’m building the front page so I need the 12 most recently published posts.” Or maybe “Excuse me, I’m trying to build a page for post number 41607 and I need a list of every comment assigned to this post.” PHP takes this information, crams it into some sort of shape according to the site theme, and presents it to you. Each of these SQL requests is called a query. <\/p>\n

mySQL Servers Are Not Created Equal<\/h3>\n

Trust me, I have a system. Just DON'T UNPLUG ANYTHING.<\/div>
Trust me, I have a system. Just DON'T UNPLUG ANYTHING.<\/div><\/p>\n

For context: For the last 12 years, Hosting Matters (HM) has been my web host. Last week I briefly tried Dreamhost (DH) before canceling the service because it ran too slowly. On HM, the front page would generally load in less than a second. Let’s call it half a second. (With the exception of the hours between 11pm EST and 1am EST, when the site would be crazy slow for no reason I could ever figure out.) Once I’d made a copy of this site on DH, I found it loaded somewhere between 5 seconds and 10 seconds. So that’s somewhere between 10× and 20× slower. This is especially bad considering the site was still private and I was the only one viewing it. I can’t imagine how much slower it would be under normal traffic load.<\/p>\n

Once we’d proved that the mySQL server was the bottleneck, I upgraded to their “premium” SQL server, where your database runs on a virtual machine all by itself and doesn’t have to share with other people using the same hostTechnically you’re still competing for CPU and memory with the other users, but now you’re supposedly guaranteed a fixed share of it. Or something.<\/span>. This resulted in no change. That prompted me to cancel the service. I finally migrated to 1And1 hosting, which is what I’m using now. And we all remember how that turned out<\/a>.<\/p>\n

So How Many Queries?<\/h3>\n

According to my calculations, your plan is garbage.<\/div>
According to my calculations, your plan is garbage.<\/div><\/p>\n

So how many queries does it take to do something simple like assemble the front page of the blog? I decided to find out. Rather than spend hours tracing through the countless scripts that drive WordPress and figuring out the flow of the program, I figured I could look at things from the other end and just examine the mySQL logs.<\/p>\n

This would be hard to do on a live site that’s open to the public. If you just watch the spew of queries as they come in then it will be hard to tell which scripts they belong to and what they’re all doing. But I have a copy of this site on my local machine. I was able to load the page exactly once with a clean log file. Afterwards, I looked in the log. Here is what I found:<\/p>\n

\r\nQuery\tset global general_log_file = \"p:\/wamp\/mysql_general.log\"\r\nVersion: 5.6.17 (MySQL Community Server (GPL)). started with:\r\nTCP Port: 3306, Named Pipe: \/tmp\/mysql.sock\r\nConnect\troot@localhost on \r\nSELECT @@SESSION.sql_mode\r\nSET SESSION sql_mode='NO_ENGINE_SUBSTITUTION'\r\nDB\tshamusyo_dnd\r\nSELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'\r\nSELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish')  ORDER BY wp_posts.post_date DESC LIMIT 0, 12\r\nSELECT FOUND_ROWS()\r\nSELECT wp_posts.* FROM wp_posts WHERE ID IN (41602,41591,41519,41384,41474,41480,41322,41455,41408,41172,41417,38241)\r\nSELECT  t.*, tt.*, tr.object_id FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category', 'post_tag', 'post_format') AND tr.object_id IN (38241, 41172, 41322, 41384, 41408, 41417, 41455, 41474, 41480, 41519, 41591, 41602) ORDER BY t.name ASC\r\nSELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (38241,41172,41322,41384,41408,41417,41455,41474,41480,41519,41591,41602) ORDER BY meta_id ASC\r\nSELECT option_value FROM wp_options WHERE option_name = 'can_compress_scripts' LIMIT 1\r\nSELECT * FROM wp_posts WHERE ID = 34542 LIMIT 1\r\nSELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (34542) ORDER BY meta_id ASC\r\nSELECT YEAR(post_date) AS `year`, MONTH(post_date) AS `month`, count(ID) as posts FROM wp_posts  WHERE post_type = 'post' AND post_status = 'publish' GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC\r\nSELECT  t.*, tt.* FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('category') AND tt.count > 0 ORDER BY t.name ASC\r\nSELECT term_id, meta_key, meta_value FROM wp_termmeta WHERE term_id IN (12,607,609,102,1,287,14,530,587,55,611,5,498,610,242,13,508,7,468,9,111,16,11,66,10,4,8,554,188,15,608,120,598) ORDER BY meta_id ASC\r\nSELECT * FROM wp_users WHERE ID = '1'\r\nSELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (1) ORDER BY umeta_id ASC\r\nSELECT  t.term_id, tt.parent, tt.count, tt.taxonomy FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (41602) ORDER BY t.name ASC\r\nSELECT p.ID FROM wp_posts AS p  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_date < '2018-01-24 06:34:21' AND p.post_type = 'post' AND tt.taxonomy = 'category' AND tt.term_id IN (111) AND p.post_status = 'publish' ORDER BY p.post_date DESC LIMIT 1\r\nSELECT p.ID FROM wp_posts AS p  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_date > '2018-01-24 06:34:21' AND p.post_type = 'post' AND tt.taxonomy = 'category' AND tt.term_id IN (111) AND p.post_status = 'publish' ORDER BY p.post_date ASC LIMIT 1\r\nSELECT  t.term_id, tt.parent, tt.count, tt.taxonomy FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (41591) ORDER BY t.name ASC\r\nSELECT p.ID FROM wp_posts AS p  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_date < '2018-01-23 01:54:29' AND p.post_type = 'post' AND tt.taxonomy = 'category' AND tt.term_id IN (111) AND p.post_status = 'publish' ORDER BY p.post_date DESC LIMIT 1\r\nSELECT p.ID FROM wp_posts AS p  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_date > '2018-01-23 01:54:29' AND p.post_type = 'post' AND tt.taxonomy = 'category' AND tt.term_id IN (111) AND p.post_status = 'publish' ORDER BY p.post_date ASC LIMIT 1\r\nSELECT  t.term_id, tt.parent, tt.count, tt.taxonomy FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (41519) ORDER BY t.name ASC\r\nSELECT p.ID FROM wp_posts AS p  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_date < '2018-01-21 06:00:51' AND p.post_type = 'post' AND tt.taxonomy = 'category' AND tt.term_id IN (111) AND p.post_status = 'publish' ORDER BY p.post_date DESC LIMIT 1\r\nSELECT p.ID FROM wp_posts AS p  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_date > '2018-01-21 06:00:51' AND p.post_type = 'post' AND tt.taxonomy = 'category' AND tt.term_id IN (111) AND p.post_status = 'publish' ORDER BY p.post_date ASC LIMIT 1\r\nSELECT  t.term_id, tt.parent, tt.count, tt.taxonomy FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (41384) ORDER BY t.name ASC\r\nSELECT p.ID FROM wp_posts AS p  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_date < '2018-01-18 06:00:55' AND p.post_type = 'post' AND tt.taxonomy = 'category' AND tt.term_id IN (609) AND p.post_status = 'publish' ORDER BY p.post_date DESC LIMIT 1\r\nSELECT p.ID FROM wp_posts AS p  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_date > '2018-01-18 06:00:55' AND p.post_type = 'post' AND tt.taxonomy = 'category' AND tt.term_id IN (609) AND p.post_status = 'publish' ORDER BY p.post_date ASC LIMIT 1\r\nSELECT  t.term_id, tt.parent, tt.count, tt.taxonomy FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (41474) ORDER BY t.name ASC\r\nSELECT p.ID FROM wp_posts AS p  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_date < '2018-01-16 06:00:06' AND p.post_type = 'post' AND tt.taxonomy = 'category' AND tt.term_id IN (102) AND p.post_status = 'publish' ORDER BY p.post_date DESC LIMIT 1\r\nSELECT * FROM wp_posts WHERE ID = 41164 LIMIT 1\r\nSELECT p.ID FROM wp_posts AS p  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_date > '2018-01-16 06:00:06' AND p.post_type = 'post' AND tt.taxonomy = 'category' AND tt.term_id IN (102) AND p.post_status = 'publish' ORDER BY p.post_date ASC LIMIT 1\r\nSELECT  t.term_id, tt.parent, tt.count, tt.taxonomy FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (41480) ORDER BY t.name ASC\r\nSELECT p.ID FROM wp_posts AS p  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_date < '2018-01-14 07:51:50' AND p.post_type = 'post' AND tt.taxonomy = 'category' AND tt.term_id IN (5) AND p.post_status = 'publish' ORDER BY p.post_date DESC LIMIT 1\r\nSELECT * FROM wp_posts WHERE ID = 40917 LIMIT 1\r\nSELECT p.ID FROM wp_posts AS p  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_date > '2018-01-14 07:51:50' AND p.post_type = 'post' AND tt.taxonomy = 'category' AND tt.term_id IN (5) AND p.post_status = 'publish' ORDER BY p.post_date ASC LIMIT 1\r\nSELECT  t.term_id, tt.parent, tt.count, tt.taxonomy FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (41322) ORDER BY t.name ASC\r\nSELECT p.ID FROM wp_posts AS p  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_date < '2018-01-11 06:00:12' AND p.post_type = 'post' AND tt.taxonomy = 'category' AND tt.term_id IN (609) AND p.post_status = 'publish' ORDER BY p.post_date DESC LIMIT 1\r\nSELECT p.ID FROM wp_posts AS p  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_date > '2018-01-11 06:00:12' AND p.post_type = 'post' AND tt.taxonomy = 'category' AND tt.term_id IN (609) AND p.post_status = 'publish' ORDER BY p.post_date ASC LIMIT 1\r\nSELECT  t.term_id, tt.parent, tt.count, tt.taxonomy FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (41455) ORDER BY t.name ASC\r\nSELECT p.ID FROM wp_posts AS p  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_date < '2018-01-10 06:00:32' AND p.post_type = 'post' AND tt.taxonomy = 'category' AND tt.term_id IN (111) AND p.post_status = 'publish' ORDER BY p.post_date DESC LIMIT 1\r\nSELECT * FROM wp_posts WHERE ID = 41258 LIMIT 1\r\nSELECT p.ID FROM wp_posts AS p  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_date > '2018-01-10 06:00:32' AND p.post_type = 'post' AND tt.taxonomy = 'category' AND tt.term_id IN (111) AND p.post_status = 'publish' ORDER BY p.post_date ASC LIMIT 1\r\nSELECT  t.term_id, tt.parent, tt.count, tt.taxonomy FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (41408) ORDER BY t.name ASC\r\nSELECT p.ID FROM wp_posts AS p  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_date < '2018-01-09 06:00:47' AND p.post_type = 'post' AND tt.taxonomy = 'category' AND tt.term_id IN (120) AND p.post_status = 'publish' ORDER BY p.post_date DESC LIMIT 1\r\nSELECT p.ID FROM wp_posts AS p  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_date > '2018-01-09 06:00:47' AND p.post_type = 'post' AND tt.taxonomy = 'category' AND tt.term_id IN (120) AND p.post_status = 'publish' ORDER BY p.post_date ASC LIMIT 1\r\nSELECT  t.term_id, tt.parent, tt.count, tt.taxonomy FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (41172) ORDER BY t.name ASC\r\nSELECT p.ID FROM wp_posts AS p  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_date < '2018-01-07 06:00:28' AND p.post_type = 'post' AND tt.taxonomy = 'category' AND tt.term_id IN (4) AND p.post_status = 'publish' ORDER BY p.post_date DESC LIMIT 1\r\nSELECT * FROM wp_posts WHERE ID = 41240 LIMIT 1\r\nSELECT p.ID FROM wp_posts AS p  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_date > '2018-01-07 06:00:28' AND p.post_type = 'post' AND tt.taxonomy = 'category' AND tt.term_id IN (4) AND p.post_status = 'publish' ORDER BY p.post_date ASC LIMIT 1\r\nSELECT * FROM wp_users WHERE ID = '5'\r\nSELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (5) ORDER BY umeta_id ASC\r\nSELECT  t.term_id, tt.parent, tt.count, tt.taxonomy FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (41417) ORDER BY t.name ASC\r\nSELECT p.ID FROM wp_posts AS p  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_date < '2018-01-05 12:23:36' AND p.post_type = 'post' AND tt.taxonomy = 'category' AND tt.term_id IN (120) AND p.post_status = 'publish' ORDER BY p.post_date DESC LIMIT 1\r\nSELECT * FROM wp_posts WHERE ID = 41369 LIMIT 1\r\nSELECT p.ID FROM wp_posts AS p  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_date > '2018-01-05 12:23:36' AND p.post_type = 'post' AND tt.taxonomy = 'category' AND tt.term_id IN (120) AND p.post_status = 'publish' ORDER BY p.post_date ASC LIMIT 1\r\nSELECT  t.term_id, tt.parent, tt.count, tt.taxonomy FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (38241) ORDER BY t.name ASC\r\nSELECT p.ID FROM wp_posts AS p  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_date < '2018-01-04 06:00:47' AND p.post_type = 'post' AND tt.taxonomy = 'category' AND tt.term_id IN (609) AND p.post_status = 'publish' ORDER BY p.post_date DESC LIMIT 1\r\nSELECT * FROM wp_posts WHERE ID = 38156 LIMIT 1\r\nSELECT p.ID FROM wp_posts AS p  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_date > '2018-01-04 06:00:47' AND p.post_type = 'post' AND tt.taxonomy = 'category' AND tt.term_id IN (609) AND p.post_status = 'publish' ORDER BY p.post_date ASC LIMIT 1\r\nConnect\troot@localhost on promo\r\nQuery\tSELECT * FROM entries\r\nQuit\t\r\nQuit\t\r\n<\/pre>\n
All of that activity is the result of loading the front page once. Note that I stripped out a lot of extraneous things like timestamps, tabs, and a few other bits in order to make this a little easier to follow. It wasn’t much, but every little bit helps.<\/p>\n
My word processor counts 60 individual select statements. That seems like a lot, but I’ve never used Drupal, Joomla, Typo3, or any of the hot young CMS platforms that are out there right now. If you use one, I’d love to know how much dB activity the thing generates.<\/p>\n
Breaking It Down<\/h3>\n
Please enjoy this picture of Jack Black explaining things as a way to break up this wall of text and code.<\/div>
Please enjoy this picture of Jack Black explaining things as a way to break up this wall of text and code.<\/div><\/p>\n
Are you curious what all that SQL gibberish means? No? Too bad, because you’re about to read an explanation. Prepare to be informed against your will.<\/p>\n
Query\tset global general_log_file = \"p:\/wamp\/mysql_general.log\"\r\nVersion: 5.6.17 (MySQL Community Server (GPL)). started with:\r\nTCP Port: 3306, Named Pipe: \/tmp\/mysql.sock\r\nConnect\troot@localhost on \r\nSELECT @@SESSION.sql_mode\r\nSET SESSION sql_mode='NO_ENGINE_SUBSTITUTION'\r\nDB\tshamusyo_dnd\r\n<\/pre>\n
This is just some basic handshaking. PHP says “Hi” to mySQL and lets it know what database it’s going to be using. The database is called “shamusyo_dnd”. “shamusyo” was my username on my old, old host way back in 2006, and they automatically put my username on the front of the database name. I called it “dnd” because this crazy site originally started out as the record of a D&D campaign<\/a>. If you’re worried I’m creating some sort of security risk, I’ll point out that on my new host all the databases, users, and logins are different. <\/p>\n
In a normal situation, it would be a security risk to reveal all of this information to the public, but in this case it only gives you insight to the setup I have on my home PC, and that server is not reachable from the outside internet. It can only serve pages to itself. The worst this might do is give you a hint how to hack my SQL database if you were already sitting at my computer. And if you get that far then you probably have a gun, and you can just point it at me and I’ll tell you the password is “wefightorwedie”.<\/p>\n
Once PHP is connected, we get our first real query:<\/p>\n
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'<\/pre>\n
This one pulls a bunch of information out of the (very small) options table. This is all basic stuff like what theme is in use, what the homepage is, the preferred date format, the title of the site, how many posts should appear on the front page, and so on. Obviously the script needs this information before it can do anything else.<\/p>\n
SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  \r\n    AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish')  \r\n    ORDER BY wp_posts.post_date DESC LIMIT 0, 12\r\nSELECT FOUND_ROWS()\r\n<\/pre>\n
Now it’s time to begin building the front page. I have the blog set to show the most recent 12 entries, so this query looks into the table of all posts and asks for the most recent 12 that are published. This query returns the unique ID numbers for the 12 posts in question.<\/p>\n
SELECT wp_posts.* FROM wp_posts WHERE ID IN (41602,41591,41519,41384,41474,41480,41322,41455,41408,41172,41417,38241)<\/pre>\n
Now that it has the ID numbers, it asks for the data on each of those posts. This test was done during the site blackout, so the first number is the ID of this post that was at the top of the page at the time: Welcome To the New Site<\/a>. The very last number is the ID of the post that was at the bottom of the page: Borderlands Part 23: The Big Googly Eye of Helios<\/a>. You can see these numbers in the address bar when you’re browsing the site. (This post has ID# 41607.)<\/p>\n
SELECT  t.*, tt.*, tr.object_id FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category', 'post_tag', 'post_format') AND tr.object_id IN (38241, 41172, 41322, 41384, 41408, 41417, 41455, 41474, 41480, 41519, 41591, 41602) ORDER BY t.name ASC\r\n<\/pre>\n
Here is where we get knee-deep into the sorcery. In a sane universe, each post would belong to one category. But years ago, WordPress decided to generalize their category system. You had categories of postsFor example, this one is filed under “projects”.<\/span>. You can have categories of links if your blog uses a blogroll. And finally you can add “tags” to a post. I haven’t used tags in ages, but if I did you’d see them at the end of a post. If I file a post under “Comic Books” and tag it with “spider-man” and “dr-doom”, then technically you can click on any of those three things and see all posts filed under those terms. <\/p>\n
But WordPress took all three of these concepts and stuffed them into the same database table. What this means is that if you want to know what category a post is in, you have to do this roundabout thing where you have to deal with four tables. There’s the wp_terms table that lists all the terms. Then there’s wp_term_taxonomy which contains the description for reach term. Then the wp_term_relationships table links the terms (like ‘DM of the Rings’) to the descriptions (like ‘A Webcomic of Lord of the Rings as a D&D campaign’). Then wp_termmeta holds extra data about each category. (The table is empty on my site.) <\/p>\n
That’s a lot of database juggling. I don’t know enough about the design of WordPress to know if this was a good idea or not, but I will say I’m glad I’m not the one who has to maintain the code that handles this.<\/p>\n
If I’m reading my mySQL right, then this INNER JOIN is asking the database, “Hey, I’ve got this list of posts. Look in this other cross-reference table and find the taxonomy that matches the category of this post.” This would give the category description. Which means this query is possibly a big waste. Category descriptions exist on my site from back in the old days, but they’re no longer shown to the user. <\/p>\n
SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (38241,41172,41322,41384,41408,41417,41455,41474,41480,41519,41591,41602) ORDER BY meta_id ASC<\/pre>\n
Here we’re taking a peek in the post_meta table. This is a generic table where site admins like me can stuff extra info not covered by the regular WordPress database. In my case, I use it to store the splash image you see at the top of every post. So here WordPress is just getting that info for our 12 posts.<\/p>\n
SELECT option_value FROM wp_options WHERE option_name = 'can_compress_scripts' LIMIT 1<\/pre>\n
Huh. I don’t know why it’s asking this now. There’s an option in WordPress that allows it to compress Javascript and CSS. Fine. But why is it asking this in the middle of building a page? It seems like this query could have been done with the very first look into the wp_options database above. <\/p>\n
SELECT * FROM wp_posts WHERE ID = 34542 LIMIT 1\r\nSELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (34542) ORDER BY meta_id ASC<\/pre>\n
What is going on here? We’re querying another post, and its metadata (the splash image). The problem is, there’s no such thing as post #34542. Doesn’t exist. I can’t explain this. <\/p>\n
SELECT YEAR(post_date) AS `year`, MONTH(post_date) AS `month`, count(ID) as posts FROM wp_posts  WHERE post_type = 'post' AND post_status = 'publish' GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC<\/pre>\n
This one tripped me up for a bit, because I was expecting the queries to go down the page. But here it looks like it’s doing a query to create the monthly archive dropdown you see at the top of the page. Which makes me wonder: Does anyone ever use that thing? I don’t know if I’ve ever explored a site using the monthly archive. (I’m not thinking of getting rid of it. I’m just generally curious when this gets used.) <\/p>\n
SELECT  t.*, tt.* FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('category') AND tt.count > 0 ORDER BY t.name ASC\r\nSELECT term_id, meta_key, meta_value FROM wp_termmeta WHERE term_id IN (12,607,609,102,1,287,14,530,587,55,611,5,498,610,242,13,508,7,468,9,111,16,11,66,10,4,8,554,188,15,608,120,598) ORDER BY meta_id ASC<\/pre>\n
And here it creates the category dropdown. I actually use this one all the time.<\/p>\n
SELECT * FROM wp_users WHERE ID = '1'\r\nSELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (1) ORDER BY umeta_id ASC<\/pre>\n
Okay, at this point the script is FINALLY walking down the page and assembling the individual posts. It’s on the top post. I’m the author, and it looks up my user so it can put my information at the top of the post and my byline at the bottom.<\/p>\n
SELECT  t.term_id, tt.parent, tt.count, tt.taxonomy FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (41602) ORDER BY t.name ASC\r\nSELECT p.ID FROM wp_posts AS p  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_date < '2018-01-24 06:34:21' AND p.post_type = 'post' AND tt.taxonomy = 'category' AND tt.term_id IN (111) AND p.post_status = 'publish' ORDER BY p.post_date DESC LIMIT 1\r\nSELECT p.ID FROM wp_posts AS p  INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_date > '2018-01-24 06:34:21' AND p.post_type = 'post' AND tt.taxonomy = 'category' AND tt.term_id IN (111) AND p.post_status = 'publish' ORDER BY p.post_date ASC LIMIT 1<\/pre>\n
Ah! And now I understand why the front page is generating so many queries. <\/p>\n
Here it’s starting with the post at the top, which we already established is #41602 – Welcome To the New Site<\/a>. The first line is looking up category descriptions again, which it should already have and which my site doesn’t use anyway, but whatever. <\/p>\n
The second line is doing a search for a post in the same category that was published directly before this one. The third line is looking for the post after this one. These queries are generated by the buttons at the top of every post. These are designed to allow you to conveniently step through a long-running series<\/a> a post at a time. <\/p>\n
So creating the previous post \/ next post buttons takes 3 database queries. One to query the totally unused category description, and the other two to get the previous and next posts. It goes through this process 12 times as it walks down the page.<\/p>\n
Connect\troot@localhost on promo\r\nQuery\tSELECT * FROM entries\r\nQuit\t\r\nQuit<\/pre>\n
And here it gets to the bottom of the page. It connects to the “promo” database, which is a database I added, entirely apart from WordPress. It stores all of those little post suggestions you see at the end of an article. I actually request the entire table, all 130+ entries. This is probably inefficient, but I honestly can’t think of a better way to handle it. I want several random entries from the table and I can’t see how to do that. I’ve read some tutorials that suggest using RAND(), but RAND () doesn’t seem to be part of the version of mySQL I’m using? I don’t know.<\/p>\n
This is an inefficient design, but the database is small enough that it doesn’t really matter. <\/p>\n
Once it has the promo, it terminates its connection to both databases and we’re done. <\/p>\n
Wrapping Up<\/h3>\n
Most people don't know this, but databases are actually shaped like 3-layer cakes.<\/div>
Most people don't know this, but databases are actually shaped like 3-layer cakes.<\/div><\/p>\n
It looks like a lot of the database traffic is my fault. To make the prev \/ next buttons for each post requires 3 queries. 3 queries x 12 posts = 36 total queries. Remember we counted 60 total queries in this single page-load, so just over half of all database transactions are spent making those links.<\/p>\n
I suppose there are some things I could do to speed this up. I could leave the chronological navigation buttons off of the front page. It’s unlikely that a user will use them from there. Think about it: Load the front page, read the first couple of intro paragraphsAssuming it’s a good day and I didn’t put the whole post on the front page.<\/span> and then LEAVE this post without reading the rest of it so you can read the previous one? I’m not saying it never happens, but it does sound like an unusual situation.<\/p>\n
The other thing I could do is restrict chronological navigation to certain categories. Clearly the big retrospectives, the webcomic, and the weekly column need them. But Notices? Does anyone really page through the Notices category, browsing my announcements on site changes?<\/p>\n
I’m not saying I’m planning on doing anything right now. It’s just something to think about.<\/p>\n
In any case, I think I’ve demonstrated that my site generates a lot of extra queries. It looks like a more conventional WP site will generate between 20 and 30 queries. Maybe that’s a lot, maybe it isn’t. <\/p>\n
I don’t know if we leaned anything but we definitely just killed ten minutes. You’re welcome.<\/p>\n","protected":false},"excerpt":{"rendered":"
The conventional wisdom is that WordPress is really database intensive. I’ve never seen an official critique of it or a proper comparison between WordPress and some other CMSContent Management System or publishing platform. This is just the sort of thing I see dismissive blowhards say on forums or StackOverflow. “What did you expect? WordPress puts […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[],"class_list":["post-41607","post","type-post","status-publish","format-standard","hentry","category-projects"],"_links":{"self":[{"href":"https:\/\/www.shamusyoung.com\/twentysidedtale\/index.php?rest_route=\/wp\/v2\/posts\/41607","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.shamusyoung.com\/twentysidedtale\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.shamusyoung.com\/twentysidedtale\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.shamusyoung.com\/twentysidedtale\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.shamusyoung.com\/twentysidedtale\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=41607"}],"version-history":[{"count":28,"href":"https:\/\/www.shamusyoung.com\/twentysidedtale\/index.php?rest_route=\/wp\/v2\/posts\/41607\/revisions"}],"predecessor-version":[{"id":41718,"href":"https:\/\/www.shamusyoung.com\/twentysidedtale\/index.php?rest_route=\/wp\/v2\/posts\/41607\/revisions\/41718"}],"wp:attachment":[{"href":"https:\/\/www.shamusyoung.com\/twentysidedtale\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=41607"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.shamusyoung.com\/twentysidedtale\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=41607"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.shamusyoung.com\/twentysidedtale\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=41607"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}