A look inside the WordPress database

Update: This article is also available in Chinese (thanks to Denis).

WordPress, as most other modern content management systems, is a very database-centric application. It keeps all information in the database – blog settings, posts, comments, links, users, etc. Therefor it is important to understand how the database is organized, what types of data is stored there, and how different things are linked to each other.

But before we dive into it, here are three points that I want to stress out:

1. WordPress API provides a number of ways to manipulate data without directly accessing the database. So, technically, you don’t have to know how the database is organized. However, if you do, you’ll probably find solutions to your problems faster, and they’ll be easier to implement.
2. WordPress database scheme is small and simple. There is nothing to be scared of. It’s only 10 tables, with just a few fields each. Overall, the scheme makes so much sense, that you will probably keep most of it in mind after a second look at it, even though you don’t have to.
3. WordPress database scheme is well documented. The only thing with that Codex page is that it explains tables in alphabetic order, which makes sense when you are looking up something. But for the first few times, it’s better to have a different order, which is what this post is trying to achieve.

OK, now let’s see what is there to see.

For the rest of the article, you’ll need either another browser tab with previously mentioned database description at Codex, or a database tool, such as PHPMyAdmin or MySQL command line. To find a list of database tables in your WordPress installation run “SHOW TABLES” query. To get a specification of any table use EXPLAIN command, like so: “EXPLAIN wp_posts“.

wp_posts

WordPress is mostly used as a blogging engine content management system (CMS, for short). So, the “content” table, I think, is of primary importance. Pages, posts, and attachments are all content. And WordPress keeps them all in one table – wp_posts.

Actually, attachments are kept as they are – files on disk, but WordPress uses wp_posts table to keep attachment meta information, such as ID of user who uploaded it, ID of post to which the attachment is linked, the date it was uploaded, etc.

Pages, posts, and attachments can be very similar and they can be very different. For now, we want to look at things which are common between them. All three have a unique ID (usually generated by WordPress or MySQL automatically), an author (person who wrote the post or uploaded the file), posting date, title, and description (even if empty, there is still a way to add it and use it later). All three have an URL. So, for each post, page, and attachment, WordPress keeps a record in wp_posts table.

Now, there are a few ways that posts, pages, and attachments differ. To differentiate between them, WordPress uses a column post_type in wp_posts table. For posts, the value of post_type is ‘post’. For pages – ‘page’. For attachments – ‘attachment’.

Then, there are a few columns which are used only when post is of some specific type. For example, column post_mime_type is used to keep attachments file types, such as ‘image/jpeg’ and ‘application/pdf’ (read about MIME at Wikipedia). Column menu_order is used to specify the order of pages. Column post_parent is used to organize pages with sub-pages, as well as to link attachments to posts.

To get a better picture of how and when each field is used, take a look at real records for each post type:

• SELECT * FROM wp_posts WHERE post_type = ‘post’ LIMIT 1
• SELECT * FROM wp_posts WHERE post_type = ‘page’ LIMIT 1
• SELECT * FROM wp_posts WHERE post_type = ‘attachment’ LIMIT 1

If you are using MySQL command line tool, then you will appreciate a “\G” at the end of the query (“SELECT * FROM wp_posts WHERE post_type=’post’ LIMIT 1 \G”). It will give you the results in a slightly more readable format.

Post status is also kept in this table. In most cases you’ll see ‘publish’ for published posts, ‘draft’ for drafts, and ‘inherit’ for attachments. But there are a few others that you can learn from the table specification.

Here are a couple of ideas for you on what you could do with just wp_posts table:

• Generate some statistics – how many posts are there in total, how many posts of each type, how many posts each author published, what is the average number of posts published per day over a period of time, etc.
• Generate a list of all attachments
• Generate an image gallery for each post (hint: “WHERE post_type = ‘attachment’ AND (post_mime_type = ‘image/jpeg’ OR post_mime_type = ‘image/gif’ OR post_mime_type = ‘image/png’)“).

wp_postmeta

There are probably a million of things that you’d want to keep about some of your posts – the mood your were when wrote it, the song you were listening at the moment, the geographical location you were at, some list of related links, post-specific information for search engines (SEO), and so on, and so forth. All these things go into wp_postmeta table.

The structure of this table is very simple and flexible. It has only four columsn: meta_id, post_id, meta_key, and meta_value. meta_id is automatically generated and I can’t think of a scenario where you would want to change that – there are many other fun things to do. post_id is the ID of the post (as in record from the wp_posts table) to which current meta information is linked. meta_key is the description of the information that you want to attach to the post (such as ‘mood’ or ‘song’). meta_value is the actual content of the meta information (such as ‘Grumpy!’ or ‘The WordPress Song.mp3’).

WordPress uses this table to store additional information about attachments, which does not fit into wp_posts table (file system path to the attachment, thumbnail information, if any, etc). Also, this table is used to store custom field names and values – those that you see in the box under your post editor. And, of course, there are tonnes of plugins out there which utilize this table for their own needs. So, if you decide to use this table in your plugin, make sure that you come up with some unique names for your meta keys. Otherwise, your plugin might interfere with some other plugin and you’ll get some really weird and totally unexpected results.

wp_users

WordPress uses this to store registered users. User ID, login, encrypted password, full name, registration date, and things like that are kept in wp_users. Think of it as essential information about the users.

wp_usermeta

The wp_usermeta table for users is exactly what wp_postmeta table is for posts. You can keep all sorts of things for each registered user here – personalized blog settings (WordPress already does so for visual editor preference, for example), birthdays, contact information, etc.

wp_comments

wp_comments table keeps all comments for your site. These include all approved comments, comments awaiting moderation, SPAM comments, and even trackbacks and pingbacks which are sent to you from other web sites. You can probably guess most of the columns in this table without me telling you. But just in case, here are the ones you probably care about:

• comment_ID – unique ID of each comment, automatically generated by MySQL
• comment_post_ID – ID of the post for which the comment was made
• comment_author – name of the comment author
• comment_author_email – email of the comment author
• comment_author_url – URL of the comment author
• comment_author_IP – the IP that comment author came from
• comment_date – the data and time the comment was made
• comment_content – the actual comment
• comment_approved – whether the comment was moderated, and if yes, then how
• comment_agent – browser signature of the commenter (MSIE, Firefox, Safari, etc)
• comment_type – if it is a regular comment, or trackback , or something else
• user_id – if comment user is a registered user, you’ll have his ID in here

There are a couple of other fields in there, but we’ll leave them for now, as we did with other tables.

wp_options

As we saw in update to “Quick access to WordPress options” post, wp_options is the table that holds all the global options for your WordPress installation. The core of this table is very similar to the wp_postmeta and wp_usermeta tables. And it is understandable – wp_options stores information about your WordPress in much the same way, as wp_postmeta stores information about posts and wp_usermeta stores information about users. However, you will probably notice a few extra columns.

blog_id column is there, but it is not of much use on a standalone WordPress installations. It is used only by WordPress MU. Regular WordPress and WordPress MU share most of the source code and database structure. So, WordPress MU uses blog_id column in wp_options table to differentiate between options for different blogs under one roof.

autoload column controls if the option is always loaded from the database and cached for use by WordPress and plugins or if it is read only when asked for. Most options that I’ve seen, are autoloaded.

wp_categories

If you have any categories in your WordPress installation, wp_categories is the table that keeps those records. Category names and descriptions are stored here, as well the ID of each category’s parent.

To work faster, WordPress often keeps aggregated values in the database, instead of recalculating them each time. For example, frequently requested counts of posts and links in each category are simply stored in the wp_categories table (WordPress uses the same set of categories for both links and posts). Every time you add post to a category, the post counter (column category_counter) increases. Every time you remove post from the category, the counter decreased. Same goes for links (column link_count). That’s why you see those extra columns in the table.

wp_post2cat

Linking posts to categories is done via wp_post2cat table. This is a standard approach for many-to-many relationships in relational databases. Thewp_post2cat table has only three fields: the unique record ID (automatically generated), the ID of the post, and the ID of the category to add the post to.

wp_links

Usually wp_links table is used to store the blogroll – web site’s list of links. Check the blogroll administration interface carefully, and you’ll see lots of options, which aren’t used by most WordPress users (no statistical proof here though). All those links options can be stored in wp_links table. In fact, you won’t need to write much code to turn WordPress into a link directory, like Yahoo! once was. It’ll be only a matter of an appropriate theme.

One important bit to notice here, is that link_owner is kept for each link. So, you know who created the link.

wp_link2cat

wp_link2cat table is the exact copy of wp_post2cat, except it keeps the ID of the link, instead of the ID of the post.

That’s all there is to WordPress database structure. If you see any other tables in your installation, then they were probably added by one of the plugins that you’ve installed.

Update: Those of you who prefer a bird’s eye view, will appreciate WordPress Database Structure (PNG file, 167 KBytes, from here).

Update: Ryan Boren has posted an explanation of database changes in WordPress 2.3 related to taxonomy – a new way of tagging and categorizing posts and links.