WordPress Database Explained: A Complete Guide
Asad Ali
Founder & Lead Developer · Former WordPress Core Contributor
WordPress Database Explained: A Complete Guide
Understanding the WordPress database helps you troubleshoot issues, optimize performance, and plan migrations. This guide explains how WordPress stores your content.
Overview
WordPress uses MySQL (or MariaDB) to store:
- Posts and pages
- User information
- Comments
- Settings and options
- Plugin data
- Theme customizations
By default, WordPress creates 12 core tables. Plugins add their own tables.
The Core Tables
wp_posts
The most important table. Stores all content.
| Column | Description |
| ID | Unique identifier |
| post_author | Author user ID |
| post_date | Publication date |
| post_content | Full content (HTML) |
| post_title | Title |
| post_excerpt | Excerpt/summary |
| post_status | publish, draft, private, etc. |
| post_type | post, page, attachment, or custom |
| post_name | URL slug |
| post_parent | Parent post ID |
| menu_order | Order for sorting |
| guid | Globally unique identifier |
Not just posts: This table also stores:
- Pages
- Attachments (media)
- Navigation menu items
- Revisions
- Custom post types
wp_postmeta
Key-value storage for post data.
| Column | Description |
| meta_id | Unique identifier |
| post_id | Related post ID |
| meta_key | Name of the metadata |
| meta_value | Value (can be serialized) |
Common meta keys:
_thumbnail_id- Featured image
_edit_last- Last editor
_wp_page_template- Page template
- Custom fields from plugins
Performance note: This table grows quickly and can become a bottleneck.
wp_users
User account information.
| Column | Description |
| ID | User ID |
| user_login | Username |
| user_pass | Hashed password |
| user_nicename | URL-friendly name |
| user_email | Email address |
| user_registered | Registration date |
| display_name | Display name |
wp_usermeta
User preferences and capabilities.
| Column | Description |
| umeta_id | Unique identifier |
| user_id | Related user ID |
| meta_key | Metadata name |
| meta_value | Metadata value |
Stores:
- User roles and capabilities
- Admin preferences
- Plugin user settings
wp_comments
Comment content.
| Column | Description |
| comment_ID | Unique identifier |
| comment_post_ID | Post being commented on |
| comment_author | Commenter name |
| comment_author_email |
| comment_content | Comment text |
| comment_date | When posted |
| comment_approved | 0, 1, or 'spam' |
| comment_parent | Parent comment (for threading) |
wp_commentmeta
Additional comment data. Same structure as other meta tables.
wp_options
Site-wide settings. Critical table.
| Column | Description |
| option_id | Unique identifier |
| option_name | Setting name |
| option_value | Setting value |
| autoload | Load automatically (yes/no) |
Stores:
- Site URL and name
- Active plugins list
- Theme settings
- Widget configurations
- Cron schedules
- Transients (cached data)
Performance note: Too many autoloaded options slows every page load.
wp_terms
Categories, tags, and custom taxonomies.
| Column | Description |
| term_id | Unique identifier |
| name | Human-readable name |
| slug | URL-friendly name |
| term_group | Grouping (rarely used) |
wp_termmeta
Term metadata. Added in WordPress 4.4.
wp_term_taxonomy
Links terms to taxonomy types.
| Column | Description |
| term_taxonomy_id | Unique identifier |
| term_id | Related term |
| taxonomy | category, post_tag, or custom |
| description | Taxonomy description |
| parent | Parent term ID |
| count | Number of posts |
wp_term_relationships
Connects posts to terms.
| Column | Description |
| object_id | Post ID |
| term_taxonomy_id | Term taxonomy ID |
| term_order | Order of terms |
wp_links
Legacy blogroll table. Rarely used today but still exists.
Database Relationships
wp_posts (1) ─────────── (∞) wp_postmeta
│
└───────────────────── (∞) wp_term_relationships
│
└── (1) wp_term_taxonomy
│
└── (1) wp_terms
wp_users (1) ─────────── (∞) wp_usermeta
│
└────── (∞) wp_posts (via post_author)
│
└────── (∞) wp_comments
wp_comments (1) ──────── (∞) wp_commentmeta
Plugin Tables
Plugins add their own tables. Common examples:
| Plugin | Tables Added |
| WooCommerce | 14+ tables (wp_wc_, wp_woocommerce_) |
| Yoast SEO | wp_yoast_* tables |
| Contact Form 7 | Uses postmeta, may add tables |
| ACF | Uses postmeta extensively |
| WP Rocket | wp_rocket_* tables |
Problem: Each plugin adds database overhead. Some never clean up.
Performance Issues
The wp_options Problem
Every page load, WordPress fetches all autoloaded options:
SELECT * FROM wp_options WHERE autoload = 'yes'
If this returns 1MB of data, every page is slow.
Common bloat:
- Transients stored as options
- Plugin settings (often serialized)
- Theme customizer data
Solution:
-- Check autoloaded options size
SELECT SUM(LENGTH(option_value)) as size
FROM wp_options
WHERE autoload = 'yes';
-- Find largest options
SELECT option_name, LENGTH(option_value) as size
FROM wp_options
WHERE autoload = 'yes'
ORDER BY size DESC
LIMIT 20;
The wp_postmeta Problem
Without proper indexing, queries slow down:
-- This gets slow with large tables
SELECT * FROM wp_posts p
JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE pm.meta_key = '_custom_field';
Table size: wp_postmeta often has 10-100x more rows than wp_posts.
Revision Bloat
Every save creates a revision in wp_posts:
-- Count revisions
SELECT COUNT(*) FROM wp_posts WHERE post_type = 'revision';
Sites often have 10,000+ revisions adding no value.
Querying the Database
Direct Queries (Careful!)
global $wpdb;
// Get all published posts
$posts = $wpdb->get_results("
SELECT ID, post_title
FROM {$wpdb->posts}
WHERE post_status = 'publish'
AND post_type = 'post'
");
// Get post meta
$meta = $wpdb->get_var($wpdb->prepare("
SELECT meta_value
FROM {$wpdb->postmeta}
WHERE post_id = %d
AND meta_key = %s
", $post_id, '_thumbnail_id'));
Always use $wpdb->prepare() for user input!
Using WP_Query (Safer)
$query = new WP_Query([
'post_type' => 'post',
'post_status' => 'publish',
'posts_per_page' => 10,
'meta_query' => [
[
'key' => 'featured',
'value' => '1',
],
],
]);
Exporting Database Content
Full Database Export
Using mysqldump
mysqldump -u username -p database_name > backup.sql
Using WP-CLI
wp db export backup.sql
Export Specific Content
Export just posts and postmeta
mysqldump -u username -p database_name \
wp_posts wp_postmeta > content.sql
Export using WP-CLI
wp export --post_type=post,page
Export for Migration
When migrating to a static site, you typically need:
1. wp_posts - All your content
2. wp_postmeta - Featured images, custom fields
3. wp_terms/wp_term_taxonomy/wp_term_relationships - Categories/tags
4. wp_users - Author information
Use our export tool for easy migration →
Why Static Sites Don't Need This
The WordPress database exists because:
- Content changes frequently
- Users need admin access
- Comments need storing
- Plugins need flexibility
Static sites store content as:
- Markdown/MDX files - Version controlled
- JSON/YAML - Structured data
- Git - History and collaboration
No database means:
- No performance tuning needed
- No backup complexity
- No security vulnerabilities
- No scaling concerns
Database Cleanup
Remove Revisions
DELETE FROM wp_posts WHERE post_type = 'revision';
Or limit in wp-config.php:
define('WP_POST_REVISIONS', 3);
Remove Transients
DELETE FROM wp_options
WHERE option_name LIKE '%_transient_%';
Remove Orphaned Meta
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL;
Remove Spam Comments
DELETE FROM wp_comments WHERE comment_approved = 'spam';
FAQ
Q: Why does my database keep growing?
Revisions, transients, plugin data, and autoloaded options. Regular cleanup helps.
Q: Should I optimize my database?
Yes, periodically. Use WP-Optimize or WP-Sweep plugins, or OPTIMIZE TABLE commands.
Q: How do I know which plugin added a table?
Check the table prefix (wp_wc_ = WooCommerce) or look in plugin code for CREATE TABLE statements.
Q: Can I query the database directly?
Yes, but use $wpdb methods and always sanitize inputs. Direct queries bypass WordPress caching.
Q: What's the alternative to database management?
Static sites store content as files instead of databases—no optimization needed. See our JAMstack guide →
Conclusion
The WordPress database is powerful but complex:
- 12 core tables, many more from plugins
- Meta tables create flexibility but cause performance issues
- Regular cleanup is essential
- Scaling requires optimization
For many sites, migrating to a static architecture eliminates these concerns entirely—no database, no problems.
Related guides:
Related Articles
View allWordPress Multisite: When It Makes Sense (And When to Avoid It)
Understand WordPress Multisite architecture, use cases, limitations, and modern alternatives for managing multiple sites.
Local Development for WordPress (and Why Modern Tools Are Better)
Compare WordPress local development tools: Local, MAMP, Docker, and DevKinsta. Then see how modern frameworks make development easier.
WordPress REST API: A Complete Guide for Developers
Master the WordPress REST API. Understand endpoints, authentication, custom routes, and how this enables headless WordPress.
How to Export WordPress Site: Complete Backup & Migration Guide
Learn how to properly export your WordPress site for backup or migration. Covers database exports, media files, theme settings, and everything you need for a complete backup.