WordPress.org

Codex

Interested in functions, hooks, classes, or methods? Check out the new WordPress Code Reference!

Database Description/2.9

wp-alert.png
This article, written to accord with WordPress 2.9, is now obsolete. Please take caution when following its contents, as many things may have changed.

The following is an outline and description of the database tables created during the standard installation of WordPress Version 2.9. At the time, the only database supported by WordPress Version 2.9 was MySQL version 4.1.2 or greater.

Also see prior versions of Database Descriptions for WordPress 1.5, WordPress 2.0, WordPress 2.2, WordPress 2.3, WordPress 2.5, WordPress 2.7, and WordPress 2.8.

Because WordPress interfaces with this database by itself, you as an end user, shouldn't have to worry much about its structure. If you're Writing a Plugin, however, you may be interested in learning how to interface with the database directly. WordPress provides the wpdb Class to make this task easy.

Database Diagram

The diagram below provides a visual overview of the Wordpress database and the relations between the tables (WP Version 2.5+) created during the WordPress standard installation. The Table Overview below includes additional details on the tables and columns.

(WP 2.5 Database diagram)


[An updated diagram for version 2.9.1 can be found here.]

Please note that within the standard installation of Wordpress no integrity between the tables is enforced e.g. between posts and comments. If you are creating a plugin or extension that manipulates the Wordpress database, your code should do the housekeeping so that no orphan records remain in the tables e.g. by removing records in other tables with a set of SQL commands when foreign keys are deleted (Don't forget to remind users to backup before such operations).

Table Overview

This section is the overview of all the tables created during the WordPress standard installation. It is followed by specific information of what is in each table.

WordPress 2.9 Tables (11)
Table Name Description Relevant Area(s) of WordPress User Interface
wp_commentmeta Each comment features information called the meta data and it is stored in the wp_commentmeta. Typically plugins would be add their own information to this table.
wp_comments The comments within WordPress are stored in the wp_comments table. Comments are created by readers as responses to posts. Comments are managed by administrator via Administration > Comments > Comments
wp_links The wp_links holds information related to the links entered into the Links feature of WordPress.
wp_options The Options set under the Administration > Settings panel are stored in the wp_options table.
wp_postmeta Each post features information called the meta data and it is stored in the wp_postmeta. Some plugins may add their own information to this table.
wp_posts The core of the WordPress data is the posts. It is stored in the wp_posts table.
wp_terms The categories for both posts and links and the tags for posts are found within the wp_terms table.
wp_term_relationships Posts are associated with categories and tags from the wp_terms table and this association is maintained in the wp_term_relationships table. The association of links to their respective categories are also kept in this table.
wp_term_taxonomy This table describes the taxonomy (category, link, or tag) for the entries in the wp_terms table.
wp_usermeta Each user features information called the meta data and it is stored in wp_usermeta.
wp_users The list of users is maintained in table wp_users.


Table Details

The following are the specific fields in each of the tables created during the standard WordPress installation.

Table: wp_commentmeta

Field Type Null Key Default Extra
meta_id bigint(20) unsigned   PRI NULL auto_increment
comment_id bigint(20) unsigned   IND 0 FK->wp_comments.comment_id
meta_key varchar(255) YES IND NULL  
meta_value longtext YES   NULL  

Indexes

Keyname Type Cardinality Field
PRIMARY PRIMARY 0 meta_ID
comment_id INDEX none comment_id
meta_key INDEX none meta_key

Table: wp_comments

Field Type Null Key Default Extra
comment_ID bigint(20) unsigned   PRI NULL auto_increment
comment_post_ID bigint(20) unsigned   IND 0 FK->wp_posts.ID
comment_author tinytext        
comment_author_email varchar(100)        
comment_author_url varchar(200)        
comment_author_IP varchar(100)        
comment_date datetime     0000-00-00 00:00:00  
comment_date_gmt datetime   IND & IND Pt2 0000-00-00 00:00:00  
comment_content text        
comment_karma int(11)     0  
comment_approved varchar(20)   IND & Ind Pt1 1  
comment_agent varchar(255)        
comment_type varchar(20)        
comment_parent bigint(20) unsigned     0 FK->wp_comments.ID
user_id bigint(20) unsigned     0 FK->wp_users.ID

Indexes

Keyname Type Cardinality Field
PRIMARY PRIMARY 1 comment_ID
comment_approved INDEX None comment_approved
comment_post_ID INDEX None comment_post_ID
comment_approved_date_gmt INDEX None comment_approved
comment_date_gmt
comment_date_gmt INDEX None comment_date_gmt

Table: wp_links

Field Type Null Key Default Extra
link_id bigint(20) unsigned   PRI NULL auto_increment
link_url varchar(255)        
link_name varchar(255)        
link_image varchar(255)        
link_target varchar(25)        
link_description varchar(255)        
link_visible varchar(20)   IND Y  
link_owner bigint(20) unsigned     1  
link_rating int(11)     0  
link_updated datetime     0000-00-00 00:00:00  
link_rel varchar(255)        
link_notes mediumtext        
link_rss varchar(255)        

Indexes

Keyname Type Cardinality Field
PRIMARY PRIMARY 7 link_ID
link_category INDEX None link_category
link_visible INDEX None link_visible

Table: wp_options

Field Type Null Key Default Extra
option_id bigint(20) unsigned   PRI Pt1 NULL auto_increment
blog_id int(11)   PRI Pt2 0  
option_name varchar(64)   PRI Pt3 & IND    
option_value longtext      
autoload varchar(20)     yes  

Indexes

Keyname Type Cardinality Field
PRIMARY PRIMARY 184 option_id
blog_id
option_name
option_name UNIQUE 184 option_name

Table: wp_postmeta

Field Type Null Key Default Extra
meta_id bigint(20) unsigned   PRI NULL auto_increment
post_id bigint(20) unsigned   IND 0 FK->wp_posts.ID
meta_key varchar(255) YES IND NULL  
meta_value longtext YES   NULL  

Indexes

Keyname Type Cardinality Field
PRIMARY PRIMARY 13 meta_ID
post_id INDEX 15 post_id
meta_key INDEX 7 meta_key

Table: wp_posts

Field Type Null Key Default Extra
ID bigint(20) unsigned   PRI & IND Pt4   auto_increment
post_author bigint(20) unsigned     0 FK->wp_users.ID
post_date datetime   IND Pt3 0000-00-00 00:00:00  
post_date_gmt datetime     0000-00-00 00:00:00  
post_content longtext        
post_title text        
post_excerpt text        
post_status varchar(20)   IND PT2 publish  
comment_status varchar(20)     open  
ping_status varchar(20)     open  
post_password varchar(20)        
post_name varchar(200)   IND    
to_ping text        
pinged text        
post_modified datetime     0000-00-00 00:00:00  
post_modified_gmt datetime     0000-00-00 00:00:00  
post_content_filtered text      
post_parent bigint(20) unsigned     0 FK->wp_posts.ID
guid varchar(255)        
menu_order int(11)     0  
post_type varchar(20)   IND Pt1 post  
post_mime_type varchar(100)        
comment_count bigint(20)     0  

Indexes

Keyname Type Cardinality Field
PRIMARY PRIMARY 2 ID
post_name INDEX None post_name
type_status_date INDEX None post_type
post_status
post_date
ID
post_parent INDEX None post_parent

Table: wp_terms

Field Type Null Key Default Extra
term_id bigint(20) unsigned   PRI   auto_increment
name varchar(200)        
slug varchar(200)   UNI    
term_group bigint(10)     0  

Indexes

Keyname Type Cardinality Field
PRIMARY PRIMARY 2 term_ID
slug UNIQUE 2 slug
name Index none name

Table: wp_term_relationships

Field Type Null Key Default Extra
object_id bigint(20) unsigned   PRI Pt1 0  
term_taxonomy_id bigint(20) unsigned   PRI Pt2 & IND 0 FK->wp_term_taxonomy.term_taxonomy_id
term_order int(11)     0  

Indexes

Keyname Type Cardinality Field
PRIMARY PRIMARY 8 object_id
term_taxonomy_id
term_taxonomy_id INDEX None term_taxonomy_id

Table: wp_term_taxonomy

Field Type Null Key Default Extra
term_taxonomy_id bigint(20) unsigned   PRI   auto_increment
term_id bigint(20) unsigned   UNI Pt1 0 FK->wp_terms.term_id
taxonomy varchar(32)   UNI Pt2    
description longtext        
parent bigint(20) unsigned     0  
count bigint(20)     0  

Indexes

Keyname Type Cardinality Field
PRIMARY PRIMARY 2 term_taxonomy_id
term_id_taxonomy UNIQUE 2 term_id
taxonomy
taxonomy INDEX None taxonomy

Table: wp_usermeta

Field Type Null Key Default Extra
umeta_id bigint(20) unsigned   PRI NULL auto_increment
user_id bigint(20) unsigned     '0' FK->wp_users.ID
meta_key varchar(255) Yes IND NULL  
meta_value longtext Yes IND NULL  

Indexes

Keyname Type Cardinality Field
PRIMARY PRIMARY 9 umeta_id
user_id INDEX None user_id
meta_key INDEX None meta_key

Table: wp_users

Field Type Null Key Default Extra
ID bigint(20) unsigned   PRI NULL auto_increment
user_login varchar(60)   IND    
user_pass varchar(64)        
user_nicename varchar(50)   IND    
user_email varchar(100)        
user_url varchar(100)        
user_registered datetime     0000-00-00 00:00:00  
user_activation_key varchar(60)      
user_status int(11)     0  
display_name varchar(250)        

Indexes

Keyname Type Cardinality Field
PRIMARY PRIMARY 1 ID
user_login_key INDEX None user_login
user_nicename INDEX None user_nicename

Resources

Changelog

  • 2.8 :
    • comments table: Changed comment_post_ID column from int(11) to bigint(20) unsigned.
    • links table: Deleted link_category column. Changed link_owner column from int(11) to bigint(20) unsigned.
    • posts table: Deleted post_category column.
    • term_taxonomy table: Added KEY taxonomy.
    • Add unsigned attribute to various bigint(20) fields.
Retrieved from "https://codex.wordpress.org/index.php?title=Database_Description/2.9&oldid=105337"

Codex Resources