Guide to WordPress Database Tables

Intro to the WordPress database

The WordPress CMS solution uses a MySQL or MariaDB database to store practically everything on your site - from posts and pages to comments, user data, settings, and plugin configurations. Understanding the database structure is key for advanced WordPress site administration, troubleshooting, performance optimization, and proper backups. Every WordPress installation creates 12 default tables that together make up the complete content management system.

The default prefix for WordPress tables is wp_ but it's recommended to change the prefix during installation for security reasons because attackers attempting SQL injection attacks often target the standard wp_ prefix. By changing to something like bh29_ you make automated attacks harder. In this guide we use the standard wp_ prefix for clarity but keep in mind your site may use a different prefix.

wp_posts - the heart of WordPress

Table structure

The wp_posts table is the most important table in the WordPress database because it stores all content including posts, pages, attached media files, revisions, menu items, and custom post types. Each row in the table represents one content object with columns like ID which is a unique identifier, post_author which references the user who created the content, post_date for creation date, post_content for the main content in HTML format, post_title for the title, post_excerpt for short description, post_status for status (publish, draft, pending, private, trash), and post_type for content type.

Post types in wp_posts

The post_type column determines the kind of content. The value post indicates blog posts, page indicates pages, attachment indicates uploaded media files, revision stores previous content versions, nav_menu_item stores navigation menu items, and custom post types have their own slug. This means that in the same table you can have thousands of posts, hundreds of pages, thousands of media files, and tens of thousands of revisions. Revisions are a common cause of database bloat because WordPress by default stores an unlimited number of revisions for each post.

Optimizing wp_posts

Limit the number of revisions by adding the WP_POST_REVISIONS constant in wp-config.php. The value 5 keeps the last 5 revisions for each post, which is enough for most sites. Periodically delete old revisions, auto-draft posts, and deleted posts from the trash using the WP-Optimize plugin or a direct SQL query. For sites with a large number of posts, indexing post_type and post_status columns speeds up queries. If you use custom post types with WP_Query, always specify post_type in the query to avoid scanning the entire table.

wp_postmeta - content meta data

Structure and purpose

The wp_postmeta table stores additional data about content not covered by columns in wp_posts. The structure is an EAV (Entity-Attribute-Value) model with four columns - meta_id, post_id which references wp_posts, meta_key which is the meta data name, and meta_value which is the value. This table is where ACF (Advanced Custom Fields) stores all custom fields, where WooCommerce stores prices and product attributes, and where WordPress stores the featured image ID, template file, serialized data, and much more.

The wp_postmeta problem

The wp_postmeta table is the most common cause of performance problems because it grows very fast. Each post can have dozens or hundreds of meta entries. ACF adds at least two rows per field (value and reference), WooCommerce adds 20 to 50 meta entries per product, and every plugin can add its own meta entries. A site with 1,000 posts can easily have 100,000 or more rows in wp_postmeta. A missing or non-optimal index on the meta_key column is a common cause of slow queries.

Optimizing wp_postmeta

Add an index on the meta_key column if it doesn't exist because the default WordPress index on this column has limited length. Use the WP-Optimize or Advanced Database Cleaner plugin to identify and delete orphan meta entries belonging to deleted posts. For frequent meta_query queries, consider creating a custom table instead of using wp_postmeta because a custom table with specific columns is much faster than the EAV model. Avoid storing large serialized arrays in meta_value because they can't be efficiently queried.

wp_options - site settings

Structure and content

The wp_options table stores all WordPress site settings including site name, URL, administrator email, active theme, active plugins, permalink structure, time zone, and hundreds of other settings. Every plugin and theme can add its own rows to wp_options to store configuration. The autoload column with value yes or no determines whether the option is automatically loaded on every request or only when specifically requested.

The autoload problem

WordPress loads all options with autoload=yes into memory on every request. If you have many plugins, this table can have hundreds of autoloaded options with a total size of several megabytes. This consumes RAM and slows every request even for pages that don't use those options. Check the total size of autoloaded options with a SQL query that sums option_value size for all rows where autoload is yes. If the result exceeds 1 MB, you have a problem that needs fixing.

Cleaning wp_options

Plugins that have been removed often leave their options in the table because WordPress doesn't have an automatic cleanup mechanism. Transient data, which is temporary cache, is stored in wp_options with keys starting with _transient_ and can accumulate thousands of rows. Use a plugin like Advanced Database Cleaner to identify options from deleted plugins and stale transients. Manually you can change autoload to no for rarely used options with a SQL query in phpMyAdmin but be careful not to change core WordPress options.

wp_users and wp_usermeta

User data

The wp_users table stores basic user data including ID, user_login for username, user_pass for hashed password, user_nicename for URL-friendly name version, user_email, user_url for user's website, user_registered for registration date, user_activation_key for account activation, and user_status. WordPress uses phpass hashing for passwords with an adjustable number of iterations to protect against brute force hash attacks.

User meta data

The wp_usermeta table has the same EAV structure as wp_postmeta and stores additional user data like user role in wp_capabilities, access level in wp_user_level, admin panel preferences, dashboard widget arrangement, and all custom user fields. WooCommerce adds shipping and billing addresses as user meta. Membership and LMS plugins can add dozens of meta entries per user. For sites with many users, wp_usermeta can become a very large table.

Comments and links tables

wp_comments and wp_commentmeta

The wp_comments table stores all comments on posts with columns for comment ID, post_id for linking to the post, comment_author, comment_author_email, comment_author_url, comment_author_IP, comment_date, comment_content, comment_approved for approval status, comment_parent for replies to comments, and user_id for registered users. Spam comments are marked with comment_approved=spam but remain in the table until you delete them. The Akismet plugin and similar anti-spam tools use wp_commentmeta to store spam scores and other meta data.

wp_terms, wp_termmeta, and wp_term_taxonomy

WordPress uses three tables for taxonomies. The wp_terms table stores the terms themselves with ID, name for the name, slug for URL, and term_group for grouping. The wp_term_taxonomy table links terms to taxonomies and stores taxonomy type, description, parent ID for hierarchy, and count for the number of posts. The wp_term_relationships table is a join table that links posts to terms with columns object_id for post ID and term_taxonomy_id. The wp_termmeta table adds the ability to store meta data for terms like category image or color.

WordPress database optimization

Regular maintenance

The WordPress database requires regular maintenance for optimal performance. Key actions include deleting post revisions older than 30 days, cleaning auto-draft and trashed posts, deleting spam and trashed comments, removing expired transients from wp_options, optimizing tables with the OPTIMIZE TABLE command, and checking table integrity with CHECK TABLE. The WP-Optimize plugin automates all these operations with the ability to schedule weekly or monthly cleanup.

Indexes and performance

Indexes are key for faster database queries. WordPress creates default indexes but for sites with a large number of posts or custom queries, additional indexes can significantly improve performance. Use EXPLAIN before a SQL query in phpMyAdmin to see whether the query uses indexes or does a full table scan. For frequent meta_query queries on a specific meta_key, add a composite index on post_id and meta_key columns in wp_postmeta. The Query Monitor WordPress plugin displays all SQL queries on a page with execution times and identifies slow queries.

Database size

Track database size regularly because a bloated database slows backups, migrations, and daily operations. The average WordPress blog should have a database smaller than 100 MB. If your database is larger than 500 MB, you likely have an issue with revisions, transients, or plugin data. WooCommerce sites with many products and orders naturally have larger databases but even then you need to clean stale data. At BeoHosting our hosting plans come with enough space for the database but an optimized database means a faster site for your visitors.

WordPress database backup

Backup methods

There are several ways to back up the WordPress database. The phpMyAdmin export feature lets you manually download a SQL dump file with all tables and data. The WP-CLI command wp db export creates a dump file from the command line which is suitable for automation. WordPress plugins like UpdraftPlus, BackWPup, and All-in-One WP Migration provide automated scheduled backups with upload to cloud storage like Google Drive, Dropbox, or Amazon S3. At BeoHosting we make automatic daily backups of the entire hosting account including the database with one-click restoration in cPanel.

Backup best practices

Make a backup before every WordPress, theme, or plugin update. Keep backups in multiple locations, not just on the same server. Periodically test backup restoration on a staging environment to confirm backups are correct. Automate daily backups with a retention policy of at least 30 days. For e-commerce sites with frequent transactions, consider backups every 6 or 12 hours. Encrypt backups containing sensitive data like user information or payment information.

Advanced topics

Multisite tables

A WordPress Multisite installation creates additional tables for managing the network of sites. The wp_blogs table contains information about each site in the network, wp_site stores network data, and wp_sitemeta stores network meta data. Each site in the network gets its own set of tables with a prefix that includes the site ID like wp_2_posts for the second site, wp_3_options for the third site, and so on. This means a Multisite installation with 10 sites has over 120 tables in the database.

Custom tables

For plugins and themes with specific data requirements, creating custom tables is often better than using wp_postmeta. A custom table with specific columns and indexes is dramatically faster for queries than the EAV model in wp_postmeta. Use the WordPress dbDelta function for creating and updating custom tables because it automatically compares the existing structure with the desired one and applies only the necessary changes. Register plugin activation with register_activation_hook for creating tables during plugin installation and provide an uninstall hook for cleanup when the plugin is deleted.

Conclusion

Understanding WordPress database tables is fundamental knowledge for every serious WordPress administrator and developer. The wp_posts and wp_postmeta tables store all content, wp_options stores configuration, and wp_users and wp_usermeta store user data. Regular database maintenance through deleting unnecessary revisions, transients, and orphan data keeps the site fast. Proper indexing speeds up queries and regular backups protect data from loss. At BeoHosting our optimized MySQL servers and automatic backups provide a reliable foundation for your WordPress site with support that understands databases inside out.