How WordPress Works: Dissecting the Database

There is beauty in the simplicity of WordPress’ database structure. All the functionality of posts, pages, custom posts, taxonomy, users and core settings are here. In 11 tables.

For comparison, the almighty Drupal has 72 tables, Joomla has 68.

All posts, pages and custom posts are saved in the wp_posts table. They are differentiated by the post_type column. Any additional data you need to save with your post (whatever the post_type is) can be stored in wp_postmeta.

Metas are extremely powerful. You can extend everything in pretty much any way with them.

Example: Your site manages the courses of an educational institute. So you create the post_types of ‘Course’ and ‘Lecturer’. Now you can save in the post_content all about the ‘Course’ and ‘Lecturer’, but what if you need to store extra information about each, that you’ll need to access easily. For a course you might want to know the dates the course is taking place. If you save that in the ‘post_content’, as part of the other descriptive content, you will not be able to run queries easily on that information, you can’t sort it, pull it out for widgets etc. That’s where meta comes in.

Each of the meta tables, postmeta, commentmeta and usermeta each have 4 columns: meta_id, post_id (or the equivalent), meta_key, and meta_value. Each post can have whatever extra meta you need, and it can be pulled out with a simple SELECT WHERE meta_key = ‘X’; command.

And that’s pretty much it. All of WordPress’s functionality is there. Comments, users, and posts all have their basic structure in their main table and all can be extended as much as needed through their meta.

Taxonomy is somewhat more complicated. It requires 3 tables. wp_term_taxonomy stores the types of taxonomies. Categories, Tags, and any other custom taxonomy type you create will be here. The individual terms will be in wp_terms. So if you have 3 categories and 15 tags in your site, each of those will be stored in wp_terms. wp_term_relationships links them all together keeping it all in order. Easy-peasy, right?

The basic options of the WordPress install are in wp_options. The only table out of order is wp_links, a relic of installs past. Today all the link functionality can easily be incorporated as a custom_post_type. But because WordPress cares about backwards compatibility, the table remains.

That’s it. Lean and mean.

One question that comes up about meta is, doesn’t that mean that there are a lot of extra queries hitting the database? This would be true, if not for the caching system of WordPress. So each time you call get_post_meta() you’re not hitting the database. So you’re good.

So when people say that WordPress is “bloated” I’m not quite sure what they’re talking about.