Skip to content
Tutorials Center
My learning

Topics

Lesson 11 of 68 ยท The Database and the Loop

The WordPress Database Schema

Understand the WordPress database tables: wp_posts, wp_postmeta, terms and taxonomies, users, options and comments, plus post types, statuses and why the meta table matters.

Behind every WordPress page is a handful of database tables, and most of what you will do as a developer is reading from and writing to them, usually through PHP functions rather than raw SQL. Understanding the schema makes the rest of the course click: why WP_Query has the arguments it has, why custom fields behave the way they do, and why some queries are slow.

A default install creates about a dozen tables, all starting with your table prefix (wp_ unless you chose another one, as seen in Setting Up a Local WordPress Site). This lesson walks through the important ones. The SQL below is shown read-only for real WordPress, and the runnable examples rebuild a simplified version of the schema in SQLite so you can run the same kinds of joins yourself.

The tables at a glance

erDiagram
    wp_posts ||--o{ wp_postmeta : "post_id"
    wp_posts ||--o{ wp_comments : "comment_post_ID"
    wp_posts ||--o{ wp_term_relationships : "object_id"
    wp_term_relationships }o--|| wp_term_taxonomy : "term_taxonomy_id"
    wp_term_taxonomy }o--|| wp_terms : "term_id"
    wp_users ||--o{ wp_usermeta : "user_id"
    wp_users ||--o{ wp_posts : "post_author"

The core tables: posts connect to meta, comments and terms; users connect to usermeta and posts. wp_options stands alone.

Table Holds
wp_posts Every piece of content: posts, pages, attachments, menu items, revisions, custom post types
wp_postmeta Extra key and value pairs attached to a post (custom fields)
wp_terms The names and slugs of categories, tags and other terms
wp_term_taxonomy Which taxonomy a term belongs to, its parent and its post count
wp_term_relationships The join table linking posts to terms
wp_termmeta Key and value pairs attached to terms
wp_users, wp_usermeta Accounts and their extra data (roles live in usermeta)
wp_options Site settings and cached data
wp_comments, wp_commentmeta Comments and their extra data

wp_posts: one table for all content

The central design decision: posts, pages, media files, navigation menu items and revisions all live in one table, told apart by a column named post_type. A "page" is simply a row with post_type = 'page'. Your own custom post types are more rows in the same table with a different post_type value.

The important columns:

Column Meaning
ID Primary key
post_author A wp_users.ID
post_date, post_date_gmt Publication time in site time zone and in UTC
post_title, post_content, post_excerpt The text
post_name The slug used in the URL
post_status Where it is in its life: publish, draft, pending, private, future (scheduled), trash, auto-draft, inherit (revisions and attachments)
post_type post, page, attachment, revision, nav_menu_item, or your own
post_parent Parent post ID, used for child pages, attachments and revisions
menu_order Manual ordering number
guid A unique identifier, never to be edited, even if the URL changes

A query for "published blog posts" always filters both columns: post_type = 'post' AND post_status = 'publish'. Forgetting post_status leaks drafts; forgetting post_type mixes in pages and menu items.

Read-only, this is the real SQL behind the front page of a simple blog:

SQL
SELECT ID, post_title
FROM wp_posts
WHERE post_type = 'post'
  AND post_status = 'publish'
ORDER BY post_date DESC
LIMIT 10;

Try it: a posts table with types and statuses

This stand-in creates a simplified posts table in SQLite with a few rows of different types and statuses, and runs the front-page query. Real WordPress has more columns; the logic is the same.

PHP One table, many kinds of content

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The first query returns two posts. The second also returns the draft, which is the classic mistake. The third shows a custom post type living in the same table.

wp_postmeta: custom fields as rows

Anything that does not have its own column goes into wp_postmeta, a table with four columns: meta_id, post_id, meta_key and meta_value. Each custom field is one row: a book's price is a row with meta_key = 'price', and its ISBN is another row for the same post_id. This pattern is called entity-attribute-value (EAV).

It is wonderfully flexible, because adding a field needs no schema change, and that flexibility is a big reason WordPress is so extensible. The cost is that every meta value is stored as text (LONGTEXT), and filtering or sorting by two or three fields needs one join per field. That is why meta queries get slow on large sites, and why you should be careful when you choose to store data as meta.

Keys that start with an underscore, like _thumbnail_id or _edit_lock, are by convention protected: they are internal and hidden from the Custom Fields box in the editor.

Try it: joining posts and meta

The stand-in below adds a meta table and finds the published books that cost less than 30. Look at the two self-joins, one per field: that is the shape of the SQL WordPress generates for a meta query with two conditions.

PHP The EAV pattern and its joins

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Note the CAST(... AS REAL): because meta values are text, comparing numbers requires converting them, which prevents the database from using an index on the value. The output lists two books and reports 8 meta rows for 4 posts.

Terms, taxonomies and relationships

Categories, tags and custom taxonomies use three tables. Splitting them looks odd at first, but it lets the same term name be reused:

  • wp_terms: the name and slug (PHP, php).
  • wp_term_taxonomy: connects a term to a taxonomy such as category or post_tag, and stores parent (for hierarchical taxonomies) and count. Its primary key is term_taxonomy_id.
  • wp_term_relationships: links a post (object_id) to a term_taxonomy_id.

To list the categories of post 5 you walk the chain: relationships, then term_taxonomy, then terms. You will rarely write that join; get_the_terms() and WP_Query's tax_query do it for you, as covered in Custom Taxonomies.

Users, options and comments

  • wp_users stores user_login, a hashed user_pass, user_email and display_name. Almost everything else about a user, including their role and capabilities, is in wp_usermeta, another EAV table.
  • wp_options holds settings as option_name, option_value and autoload. Rows with autoload = 'yes' are loaded into memory on every request, so a bloated set of autoloaded options slows the whole site. See The Options API and Transients.
  • wp_comments stores comments, with comment_post_ID, comment_approved (1, 0, spam, trash) and comment_parent for threads.

Rules for working with the schema

  • Use the API, not SQL. Functions like get_posts(), get_post_meta() and update_option() handle caching, escaping and hooks. Raw SQL skips all three. When SQL is unavoidable, use $wpdb->prepare() ($wpdb and Custom Tables).
  • Never edit the database by hand on a live site without a backup.
  • Do not store large or highly queried data as post meta; consider a custom table.
  • Keep the prefix in mind. In code, use $wpdb->posts and $wpdb->prefix rather than hardcoding wp_posts.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. How does WordPress tell a page apart from a blog post in the database?

    All content lives in wp_posts, distinguished by post_type. Custom post types are just more values of that column.
  2. 2. What are the three tables involved in categories and tags?

    terms holds names, term_taxonomy assigns them to a taxonomy, and term_relationships links them to posts.
  3. 3. Why can filtering on several meta fields be slow on large sites?

    Meta is an entity-attribute-value table, so each field is another join, and values are text, which limits index use.
  4. 4. Which post_status does a scheduled post waiting for its publish date have?

    'future' marks scheduled posts. 'pending' awaits review, 'private' is visible only to privileged users, 'inherit' is used by revisions and attachments.
  5. 5. Which problem do autoloaded options cause when there are too many?

    Rows with autoload set to yes are fetched on every page load, so large autoloaded values add cost everywhere.

Summary

  • All content, including pages, media and custom post types, lives in wp_posts, told apart by post_type and post_status.
  • wp_postmeta stores custom fields as one row per key; it is flexible but costs a join per field.
  • Terms use three tables: wp_terms, wp_term_taxonomy and wp_term_relationships.
  • Users and their roles are in wp_users and wp_usermeta; settings are in wp_options, where autoloaded rows load on every request.
  • Prefer WordPress functions over raw SQL, and use $wpdb->prepare() when you must write SQL.

Next up: The Loop.