WP DB — The Complete Guide

Part 1: The Core Architecture & Philosophy

Why No Transactions or Foreign Keys?

A defining characteristic of the WordPress database is its deliberate simplicity. It lacks advanced relational features for two main reasons: maximum host compatibility and a preference for flexibility over strictness.

Data Integrity is Your Responsibility: The database won't stop you from creating "orphan" data. You must use WordPress functions (wp_insert_post, wp_delete_post) to keep data consistent.
No Database Rollbacks: If a complex operation fails halfway through, you cannot automatically "roll back" the changes. Your code must handle any necessary cleanup.

The Index: The Secret to Database Speed

A database index is like the index at the back of a book; a special lookup table that allows the database to find rows quickly without scanning the entire table. WordPress adds indexes to columns that are frequently used for searching and filtering.

Fast vs. Slow Queries: Searching by meta_key in wp_postmeta is fast because it's indexed. Searching by meta_value is slow because it's not indexed, meaning the database must inspect every row to find a matching value.

Part 2: The Core Tables Explained

`wp_posts` & `wp_postmeta`

The central content repository. wp_posts stores all primary content, while wp_postmeta attaches arbitrary information to that content using an Entity-Attribute-Value (EAV) model.

Common `post_type` values in `wp_posts`: post, page, attachment (media files), revision, nav_menu_item, and any custom post types (e.g., `product`).
The EAV Structure of `wp_postmeta`: post_id is the Entity, meta_key is the Attribute, and meta_value is the Value. This powers custom fields.

The Taxonomy System: `wp_terms`, `wp_term_taxonomy`, `wp_term_relationships`

This three-table system provides a flexible way to categorize and group content like categories and tags.

wp_terms (The Dictionary): A master list of all the names of terms used on the site (e.g., "News", "Featured").
wp_term_taxonomy (The Context): This table gives a term its meaning. It links a term to a specific taxonomy (e.g., is "Featured" a `category` or a `post_tag`?).
wp_term_relationships (The Connection): The final link, connecting a specific post (object_id) to a specific term from wp_term_taxonomy.

`wp_users` & `wp_usermeta`

wp_users stores core user info (login, hashed password, email). wp_usermeta is an EAV table for users, storing extra data, most importantly roles and capabilities in the wp_capabilities key.

`wp_comments` & `wp_commentmeta`

wp_comments stores all comment data and its link to a post. wp_commentmeta is an EAV table allowing plugins to add extra data to comments (e.g., a "voted up" score).

`wp_options`: The Site's Brain

A simple key-value store for all site-wide settings. The autoload column is the most critical for performance. If set to 'yes', WordPress loads that option on every single page load.

Part 3: Critical Performance & Development Concepts

Object Caching: Your Database's Best Friend

WordPress has a built-in Object Cache. When paired with a persistent backend like Redis or Memcached, it stores frequently accessed data in high-speed memory, dramatically reducing database load. The Transients API is the easiest way to leverage this for your own data.

When to Use Custom Tables vs. Post Meta

While flexible, `wp_postmeta` is not ideal for large-scale or highly structured data. You should create a custom table when your data is consistent, requires complex filtering, or the data set is very large (e.g., logs, analytics).

Security: Always Use `$wpdb->prepare()`

Never insert variables directly into an SQL query. This opens you up to SQL injection attacks. Always use the $wpdb->prepare() method to safely escape your data.

INSECURE: $wpdb->get_results("SELECT * FROM $table WHERE id = $id");

SECURE: $wpdb->get_results( $wpdb->prepare("SELECT * FROM $table WHERE id = %d", $id) );