The WordPress Schema

The twelve tables a WordPress 6.8 install creates, what lives in each, and why you never hard-code the wp_ prefix.

Syntax$wpdb->posts // shorthand for $wpdb->prefix . 'posts'

A standard single-site WordPress 6.8 install creates twelve tables. Everything the CMS knows — content, taxonomy, users, settings — lives in them, and nothing more is added unless a plugin asks for it. The schema is defined in wp-admin/includes/schema.php.

What is in each

  • wp_posts — posts, pages, revisions, menu items and attachments together in one table, separated by the post_type column. Indexed on type_status_date.
  • wp_postmeta — key/value rows joined by post_id. There is no index on meta_value, which is why meta queries degrade.
  • wp_options — settings and transients, keyed by option_name (varchar(191), unique) plus an autoload column.
  • wp_users, wp_usermeta — accounts and their metadata. Roles and capabilities are rows in usermeta, not a table.
  • wp_terms, wp_term_taxonomy, wp_term_relationships, wp_termmeta — the four-table taxonomy system. A term's name is separate from the taxonomy it belongs to.
  • wp_comments, wp_commentmeta, wp_links — comments, and the Link Manager that has been hidden since 3.5 but is still created.

The prefix

$table_prefix in wp-config.php sets the prefix; wp_ is only the default. Hard-coding it breaks on any site that changed it. Read the name off $wpdb instead: $wpdb->posts is shorthand for $wpdb->prefix . 'posts' and produces the identical string.

Note the mixed column-naming conventions — ID in posts and users, comment_ID in comments, term_id in terms. They are historical and MySQL will not forgive a guess.

Example

Example · php
global $wpdb;

// Shorthand: wpdb exposes one property per core table, already prefixed.
$posts_table = $wpdb->posts;              // e.g. "wp_posts"

// Longhand: the same string, assembled by hand. These are identical.
$posts_table = $wpdb->prefix . 'posts';

// Your own tables have no property, so the longhand is the only form.
$events_table = $wpdb->prefix . 'acme_events';

// post_type is a column, not a table: "all published pages" is a WHERE clause.
$page_count = (int) $wpdb->get_var(
    "SELECT COUNT(*) FROM {$wpdb->posts}
      WHERE post_type = 'page' AND post_status = 'publish'"
);

// Revisions and menu items live in wp_posts too - this is why a raw
// SELECT COUNT(*) FROM wp_posts is always larger than the admin says.
$breakdown = $wpdb->get_results(
    "SELECT post_type, post_status, COUNT(*) AS total
       FROM {$wpdb->posts}
   GROUP BY post_type, post_status
   ORDER BY total DESC"
);

When to use it

  • Writing a reporting query that WP_Query cannot express, without guessing column names.
  • Auditing a slow site by measuring table row counts and autoloaded option size directly.
  • Deciding whether new data belongs in postmeta or in a table of its own.
  • Cleaning up orphaned postmeta or term relationships left behind by a removed plugin.

More examples

The postmeta join, written out

Shows the shape core uses for all post metadata and why meta_key is indexed while meta_value is not. Reach for this when you are deciding whether a piece of data belongs in postmeta or its own table.

Example · php
global $wpdb;

// Post meta is an EAV table: one row per key, joined back on post_id.
// This is the query get_post_meta() saves you from writing.
$rows = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT p.ID, p.post_title, pm.meta_key, pm.meta_value
           FROM {$wpdb->posts} AS p
           LEFT JOIN {$wpdb->postmeta} AS pm ON pm.post_id = p.ID
          WHERE p.ID = %d",
        $post_id
    )
);

// Filtering on meta_value has no index to use, so this scans every
// matching meta_key row. Fine for hundreds, painful for millions.
$expensive = $wpdb->get_col(
    "SELECT post_id FROM {$wpdb->postmeta}
      WHERE meta_key = 'acme_price' AND meta_value > 100"
);

Walking the four taxonomy tables

The taxonomy tables are the least intuitive part of the schema. Use this when debugging why a term count is wrong, or when writing a reporting query that WP_Query's tax_query cannot express.

Example · php
global $wpdb;

// terms holds the name and slug; term_taxonomy says which taxonomy that
// term belongs to; term_relationships links it to a post via object_id.
$post_ids = $wpdb->get_col(
    $wpdb->prepare(
        "SELECT tr.object_id
           FROM {$wpdb->term_relationships} AS tr
           INNER JOIN {$wpdb->term_taxonomy} AS tt
                   ON tt.term_taxonomy_id = tr.term_taxonomy_id
           INNER JOIN {$wpdb->terms} AS t
                   ON t.term_id = tt.term_id
          WHERE tt.taxonomy = %s AND t.slug = %s",
        'product_cat',
        'clearance'
    )
);

// Note the join key: term_relationships stores term_taxonomy_id, NOT term_id.
// Joining on term_id is the single most common mistake in this schema.

Inspecting what loads on every request

A diagnostic worth keeping in a snippet file. Run it when a site has a slow time-to-first-byte on every page including cached ones — a multi-megabyte alloptions payload is a common cause.

Example · php
global $wpdb;

// wp_options is read on every single page load for autoloaded rows.
// This tells you what that costs on a site that feels slow.
$heaviest = $wpdb->get_results(
    "SELECT option_name, LENGTH(option_value) AS bytes
       FROM {$wpdb->options}
      WHERE autoload IN ('yes','on','auto-on','auto')
   ORDER BY bytes DESC
      LIMIT 20"
);

// WordPress 6.6 introduced the 'auto', 'auto-on' and 'auto-off' values
// alongside the historic 'yes' and 'no', so match on all of them.
$total = (int) $wpdb->get_var(
    "SELECT SUM(LENGTH(option_value)) FROM {$wpdb->options}
      WHERE autoload IN ('yes','on','auto-on','auto')"
);

Related

wp wpdbwp post metawp taxonomies introwp options apiwp custom tables

Discussion

  • Be the first to comment on this lesson.