Every WordPress operation — publishing a post, saving a setting, processing an order, checking user capabilities — ultimately reads from or writes to a MySQL database. Understanding this database structure is fundamental to advanced WordPress development.
When you're debugging a migration, optimising a slow query, building a WP-CLI script that manipulates data directly, or designing a plugin that stores complex data efficiently, the database structure isn't an abstraction — it's the ground truth.
This guide covers every table in WordPress's default database schema, what each one stores, how they relate to each other, and how to query them effectively.
A fresh WordPress installation creates 12 tables. All table names use the table prefix defined in wp-config.php (default wp_). In a Multisite network, each site gets its own set of tables with a numeric prefix (wp_2_posts, wp_3_posts, etc.).
wp_posts — All content (posts, pages, revisions, attachments, etc.)
wp_postmeta — Custom fields and metadata for posts
wp_terms — Categories, tags, and custom taxonomy terms
wp_term_taxonomy — Taxonomy context for terms
wp_term_relationships — Associations between posts and terms
wp_termmeta — Metadata for terms
wp_options — Site settings and configuration
wp_users — User accounts
wp_usermeta — User metadata and capabilities
wp_comments — Post comments
wp_commentmeta — Metadata for comments
wp_links — Blogroll links (legacy, rarely used)
wp_posts is the most important table in WordPress. It stores not just blog posts, but every type of "content object" — pages, media attachments, revisions, navigation menus, and any custom post type.
wp_posts
├── ID BIGINT Primary key, auto-increment
├── post_author BIGINT User ID (FK to wp_users.ID)
├── post_date DATETIME Publication date/time (local timezone)
├── post_date_gmt DATETIME Publication date/time (UTC)
├── post_content LONGTEXT Full content of the post
├── post_title TEXT Post title
├── post_excerpt TEXT Excerpt
├── post_status VARCHAR publish, draft, private, trash, auto-draft, inherit
├── comment_status VARCHAR open, closed
├── ping_status VARCHAR open, closed
├── post_password VARCHAR Password for password-protected posts
├── post_name VARCHAR URL slug
├── to_ping TEXT URLs to ping
├── pinged TEXT URLs already pinged
├── post_modified DATETIME Last modification (local timezone)
├── post_modified_gmt DATETIME Last modification (UTC)
├── post_content_filtered LONGTEXT Filtered content (used by some plugins)
├── post_parent BIGINT Parent post ID (for pages, attachments, revisions)
├── guid VARCHAR Globally unique identifier (permanent URL)
├── menu_order INT Order for hierarchical post types and nav menus
├── post_type VARCHAR post, page, attachment, revision, nav_menu_item, custom
├── post_mime_type VARCHAR MIME type for attachments (image/jpeg, etc.)
└── comment_count BIGINT Cached comment count
-- Get all published posts
SELECT ID, post_title, post_date, post_name
FROM wp_posts
WHERE post_type = 'post'
AND post_status = 'publish'
ORDER BY post_date DESC;
-- Count posts by type and status
SELECT post_type, post_status, COUNT(*) as count
FROM wp_posts
GROUP BY post_type, post_status
ORDER BY post_type, post_status;
-- Find all attachments (uploaded media)
SELECT ID, post_title, post_mime_type, guid
FROM wp_posts
WHERE post_type = 'attachment';
-- Find revisions for a specific post
SELECT ID, post_title
FROM wp_posts
WHERE post_parent = 42
AND post_type = 'revision';
| Status | Description |
|---|---|
publish | Publicly visible |
draft | Unpublished, visible to editors/admins |
private | Only visible to logged-in users with appropriate role |
trash | Trashed, not visible |
auto-draft | Automatically saved draft |
pending | Awaiting review/approval |
future | Scheduled for future publication |
inherit | Revision or attachment (inherits parent's status) |
wp_postmeta stores arbitrary key-value data attached to any post — custom fields, theme settings, plugin data, featured image IDs, WooCommerce product data, and more.
wp_postmeta
├── meta_id BIGINT Primary key, auto-increment
├── post_id BIGINT FK to wp_posts.ID
├── meta_key VARCHAR The field name (e.g., '_thumbnail_id', 'client_name')
└── meta_value LONGTEXT The field value (can be serialised PHP arrays/objects)
Meta keys beginning with _ are "private" — they're hidden from the Custom Fields UI in the post editor. WordPress core and plugins use this convention for their own meta data.
_thumbnail_id — Featured image attachment ID_wp_attachment_image_alt — Image alt text_edit_lock — Who's currently editing the post_edit_last — User who last editedCustom fields visible to users have keys without the underscore prefix.
-- Get all meta for a specific post
SELECT meta_key, meta_value
FROM wp_postmeta
WHERE post_id = 42;
-- Get specific meta value
SELECT meta_value
FROM wp_postmeta
WHERE post_id = 42
AND meta_key = '_thumbnail_id';
-- Find all posts with a specific meta value
SELECT p.ID, p.post_title, pm.meta_value
FROM wp_posts p
JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE pm.meta_key = 'is_featured'
AND pm.meta_value = '1'
AND p.post_status = 'publish';
-- Find posts with no featured image
SELECT p.ID, p.post_title
FROM wp_posts p
WHERE p.post_type = 'post'
AND p.post_status = 'publish'
AND p.ID NOT IN (
SELECT post_id FROM wp_postmeta WHERE meta_key = '_thumbnail_id'
);
The taxonomy system uses three tables working together:
wp_terms — The actual terms (e.g., "WordPress", "PHP", "JavaScript" as tags):
wp_terms
├── term_id BIGINT Primary key
├── name VARCHAR Display name ("WordPress Development")
├── slug VARCHAR URL slug ("wordpress-development")
└── term_group BIGINT For grouping synonymous terms (rarely used)
wp_term_taxonomy — Gives context to a term (which taxonomy it belongs to):
wp_term_taxonomy
├── term_taxonomy_id BIGINT Primary key
├── term_id BIGINT FK to wp_terms.term_id
├── taxonomy VARCHAR "category", "post_tag", or custom taxonomy name
├── description LONGTEXT Term description
├── parent BIGINT Parent term (for hierarchical taxonomies)
└── count BIGINT Cached count of posts with this term
wp_term_relationships — Connects posts to taxonomy terms:
wp_term_relationships
├── object_id BIGINT FK to wp_posts.ID
├── term_taxonomy_id BIGINT FK to wp_term_taxonomy.term_taxonomy_id
└── term_order INT Order when multiple terms (rarely used)
-- Get all categories for a specific post
SELECT t.name, t.slug, tt.taxonomy
FROM wp_terms t
JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id
JOIN wp_term_relationships tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
WHERE tr.object_id = 42
AND tt.taxonomy = 'category';
-- Get all posts in a category by slug
SELECT p.ID, p.post_title
FROM wp_posts p
JOIN wp_term_relationships tr ON p.ID = tr.object_id
JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_terms t ON tt.term_id = t.term_id
WHERE t.slug = 'wordpress-tips'
AND tt.taxonomy = 'category'
AND p.post_status = 'publish';
wp_options stores all WordPress settings and plugin configuration — a massive key-value store:
wp_options
├── option_id BIGINT Primary key
├── option_name VARCHAR Setting key (e.g., "siteurl", "blogname")
├── option_value LONGTEXT Setting value (can be serialised)
└── autoload VARCHAR "yes" = loaded on every page, "no" = loaded on demand
| option_name | Description |
|---|---|
siteurl | WordPress installation URL |
blogname | Site title |
blogdescription | Site tagline |
home | Site address (URL) |
admin_email | Admin email |
blogpublic | Search engine visibility |
active_plugins | Array of active plugin file paths |
template | Active theme folder name |
stylesheet | Active theme (child theme) folder name |
autoload = 'yes' is loaded on every page request. Plugin developers who store large data in autoloaded options add to every page's load time. Use autoload = 'no' for infrequently accessed data.-- Check active plugins
SELECT option_value
FROM wp_options
WHERE option_name = 'active_plugins';
-- Find all autoloaded options and their sizes (debug slow sites)
SELECT option_name, LENGTH(option_value) as size
FROM wp_options
WHERE autoload = 'yes'
ORDER BY size DESC
LIMIT 20;
-- Count total autoloaded data
SELECT
COUNT(*) as count,
SUM(LENGTH(option_value)) as total_bytes,
ROUND(SUM(LENGTH(option_value)) / 1024) as total_kb
FROM wp_options
WHERE autoload = 'yes';
-- Find all options from a specific plugin (by prefix)
SELECT option_name, option_value
FROM wp_options
WHERE option_name LIKE 'woocommerce_%';
wp_users
├── ID BIGINT Primary key
├── user_login VARCHAR Username
├── user_pass VARCHAR Hashed password
├── user_nicename VARCHAR URL-friendly name
├── user_email VARCHAR Email address
├── user_url VARCHAR Website URL
├── user_registered DATETIME Registration date
├── user_activation_key VARCHAR For password reset
├── user_status INT 0 = active
└── display_name VARCHAR Display name
wp_usermeta
├── umeta_id BIGINT Primary key
├── user_id BIGINT FK to wp_users.ID
├── meta_key VARCHAR Key (e.g., "wp_capabilities", "wp_user_level")
└── meta_value LONGTEXT Value (capabilities stored as serialised array)
Critical usermeta keys:
wp_capabilities — Serialised PHP array: a:1:{s:13:"administrator";b:1;}wp_user_level — User level integer (0–10, legacy)session_tokens — Active login sessions-- Find all administrators
SELECT u.user_login, u.user_email
FROM wp_users u
JOIN wp_usermeta um ON u.ID = um.user_id
WHERE um.meta_key = 'wp_capabilities'
AND um.meta_value LIKE '%administrator%';
-- Find users who haven't logged in recently
SELECT u.user_login, u.user_email, u.user_registered
FROM wp_users u
LEFT JOIN wp_usermeta um ON u.ID = um.user_id
AND um.meta_key = 'session_tokens'
WHERE um.meta_value IS NULL OR um.meta_value = 'a:0:{}';
wp_comments
├── comment_ID BIGINT Primary key
├── comment_post_ID BIGINT FK to wp_posts.ID
├── comment_author TINYTEXT Commenter name
├── comment_author_email VARCHAR Commenter email
├── comment_author_url VARCHAR Commenter website
├── comment_author_IP VARCHAR IP address
├── comment_date DATETIME Comment date (local)
├── comment_date_gmt DATETIME Comment date (UTC)
├── comment_content TEXT Comment text
├── comment_karma INT Rating/karma
├── comment_approved VARCHAR 1 = approved, 0 = pending, spam = spam, trash = trash
├── comment_agent VARCHAR User-agent string
├── comment_type VARCHAR "comment", "pingback", "trackback"
├── comment_parent BIGINT Parent comment (for threaded comments)
└── user_id BIGINT FK to wp_users.ID (0 if not logged in)
WordPress provides $wpdb for database interactions. Always use it rather than raw MySQLi:
global $wpdb;
// Prepared statement (safe against SQL injection)
$post_id = 42;
$results = $wpdb->get_results(
$wpdb->prepare(
"SELECT * FROM {$wpdb->posts} WHERE ID = %d AND post_status = %s",
$post_id,
'publish'
)
);
// Always use table name constants, not hardcoded table names
// $wpdb->posts = wp_posts
// $wpdb->postmeta = wp_postmeta
// $wpdb->options = wp_options
// $wpdb->users = wp_users
// $wpdb->usermeta = wp_usermeta
// $wpdb->terms = wp_terms
// $wpdb->term_taxonomy = wp_term_taxonomy
// $wpdb->comments = wp_comments
Using $wpdb->prefix and the table property aliases ensures your code works regardless of the configured table prefix.
$wpdb->prepare() for queries containing user input or variables. Never concatenate values directly into SQL strings. This prevents SQL injection — the most critical database security vulnerability.| Table | Connects To | Relationship |
|---|---|---|
wp_posts | wp_users | post_author → ID |
wp_posts | wp_posts | post_parent → ID (self-referential) |
wp_postmeta | wp_posts | post_id → ID |
wp_term_relationships | wp_posts | object_id → ID |
wp_term_relationships | wp_term_taxonomy | term_taxonomy_id → term_taxonomy_id |
wp_term_taxonomy | wp_terms | term_id → term_id |
wp_termmeta | wp_terms | term_id → term_id |
wp_comments | wp_posts | comment_post_ID → ID |
wp_comments | wp_users | user_id → ID |
wp_commentmeta | wp_comments | comment_id → comment_ID |
wp_usermeta | wp_users | user_id → ID |
| Table | Storage Pattern | Typical Data |
|---|---|---|
wp_posts | Row per content item | Posts, pages, attachments, revisions, menus |
wp_postmeta | Key-value pairs per post | Custom fields, ACF data, WooCommerce fields |
wp_options | Global key-value store | Site settings, plugin config, theme mods |
wp_users | Row per user | User accounts |
wp_usermeta | Key-value pairs per user | Capabilities, preferences, session tokens |
wp_terms | Row per term | Category/tag names and slugs |
wp_comments | Row per comment | Comment text, author, approval status |
wp_postmeta, wp_usermeta, or wp_options for additional data, or create new custom tables for complex data.
maybe_unserialize() to decode it in PHP. Never search-replace raw serialised data in the database without using WP-CLI's search-replace command, which handles serialised data correctly.
add_option() or update_option() calls with that option name. Or grep the entire wp-content/plugins/ directory: grep -r "your_option_name" /wp-content/plugins/
post_date stores the publication date in the site's configured timezone. post_date_gmt stores the same moment in UTC. WordPress uses GMT internally for consistency and converts to the local timezone for display. Always use GMT for programmatic comparisons to avoid timezone-related bugs.
Understanding WordPress's database structure transforms how you debug, optimise, and develop for the platform. When a migration fails, you know to check wp_options for old URLs. When a post's taxonomy isn't showing, you understand the three-table join. When a plugin is mysteriously slow, you can identify the bloated wp_postmeta query.
The WordPress database is simple, well-designed, and thoroughly documented. Time invested understanding it pays dividends on every complex WordPress project.
Need help with database optimisation, complex queries, or data migration? Our development team handles advanced WordPress database operations daily.
Contact us today and we'll sort it out.