WordPress.org

Codex

Database Description/2.9

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.

Contents

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

FieldTypeNullKeyDefaultExtra
meta_idbigint(20) unsigned PRINULLauto_increment
comment_idbigint(20) unsigned IND0FK->wp_comments.comment_id
meta_keyvarchar(255)YESINDNULL 
meta_valuelongtextYES NULL 

Indexes

KeynameTypeCardinality Field
PRIMARYPRIMARY0meta_ID
comment_idINDEXnonecomment_id
meta_keyINDEXnonemeta_key

Table: wp_comments

FieldTypeNullKeyDefaultExtra
comment_IDbigint(20) unsigned PRINULLauto_increment
comment_post_IDbigint(20) unsigned IND0FK->wp_posts.ID
comment_authortinytext    
comment_author_emailvarchar(100)    
comment_author_urlvarchar(200)    
comment_author_IPvarchar(100)    
comment_datedatetime  0000-00-00 00:00:00 
comment_date_gmtdatetime IND & IND Pt20000-00-00 00:00:00 
comment_contenttext    
comment_karmaint(11)  0 
comment_approvedvarchar(20) IND & Ind Pt11 
comment_agentvarchar(255)    
comment_typevarchar(20)    
comment_parentbigint(20) unsigned  0FK->wp_comments.ID
user_idbigint(20) unsigned  0FK->wp_users.ID

Indexes

KeynameTypeCardinality Field
PRIMARYPRIMARY1comment_ID
comment_approvedINDEXNonecomment_approved
comment_post_IDINDEXNonecomment_post_ID
comment_approved_date_gmtINDEXNonecomment_approved
comment_date_gmt
comment_date_gmtINDEXNonecomment_date_gmt

Table: wp_links

FieldTypeNullKeyDefaultExtra
link_idbigint(20) unsigned PRINULLauto_increment
link_urlvarchar(255)    
link_namevarchar(255)    
link_imagevarchar(255)    
link_targetvarchar(25)    
link_descriptionvarchar(255)    
link_visiblevarchar(20) INDY 
link_ownerbigint(20) unsigned  1 
link_ratingint(11)  0 
link_updateddatetime  0000-00-00 00:00:00 
link_relvarchar(255)    
link_notesmediumtext    
link_rssvarchar(255)    

Indexes

KeynameTypeCardinality Field
PRIMARYPRIMARY7link_ID
link_categoryINDEXNonelink_category
link_visibleINDEXNonelink_visible

Table: wp_options

FieldTypeNullKeyDefaultExtra
option_idbigint(20) unsigned PRI Pt1NULLauto_increment
blog_idint(11) PRI Pt20 
option_namevarchar(64) PRI Pt3 & IND  
option_valuelongtext   
autoloadvarchar(20)  yes 

Indexes

KeynameTypeCardinality Field
PRIMARYPRIMARY184option_id
blog_id
option_name
option_nameUNIQUE184option_name

Table: wp_postmeta

FieldTypeNullKeyDefaultExtra
meta_idbigint(20) unsigned PRINULLauto_increment
post_idbigint(20) unsigned IND0FK->wp_posts.ID
meta_keyvarchar(255)YESINDNULL 
meta_valuelongtextYES NULL 

Indexes

KeynameTypeCardinality Field
PRIMARYPRIMARY13meta_ID
post_idINDEX15post_id
meta_keyINDEX7meta_key

Table: wp_posts

FieldTypeNullKeyDefaultExtra
IDbigint(20) unsigned PRI & IND Pt4 auto_increment
post_authorbigint(20) unsigned  0FK->wp_users.ID
post_datedatetime IND Pt30000-00-00 00:00:00 
post_date_gmtdatetime  0000-00-00 00:00:00 
post_contentlongtext    
post_titletext    
post_excerpttext    
post_statusvarchar(20) IND PT2publish 
comment_statusvarchar(20)  open 
ping_statusvarchar(20)  open 
post_passwordvarchar(20)    
post_namevarchar(200) IND  
to_pingtext    
pingedtext    
post_modifieddatetime  0000-00-00 00:00:00 
post_modified_gmtdatetime  0000-00-00 00:00:00 
post_content_filteredtext   
post_parentbigint(20) unsigned  0FK->wp_posts.ID
guidvarchar(255)    
menu_orderint(11)  0 
post_typevarchar(20) IND Pt1post 
post_mime_typevarchar(100)    
comment_countbigint(20)  0 

Indexes

KeynameTypeCardinality Field
PRIMARYPRIMARY2ID
post_nameINDEXNonepost_name
type_status_dateINDEXNonepost_type
post_status
post_date
ID
post_parentINDEXNonepost_parent

Table: wp_terms

FieldTypeNullKeyDefaultExtra
term_idbigint(20) unsigned PRI auto_increment
namevarchar(200)     
slugvarchar(200) UNI  
term_groupbigint(10)  0 

Indexes

KeynameTypeCardinality Field
PRIMARYPRIMARY2term_ID
slugUNIQUE2slug
nameIndexnonename

Table: wp_term_relationships

FieldTypeNullKeyDefaultExtra
object_idbigint(20) unsigned PRI Pt10 
term_taxonomy_idbigint(20) unsigned PRI Pt2 & IND0FK->wp_term_taxonomy.term_taxonomy_id
term_orderint(11)  0 

Indexes

KeynameTypeCardinality Field
PRIMARYPRIMARY8object_id
term_taxonomy_id
term_taxonomy_idINDEXNoneterm_taxonomy_id

Table: wp_term_taxonomy

FieldTypeNullKeyDefaultExtra
term_taxonomy_idbigint(20) unsigned PRI auto_increment
term_idbigint(20) unsigned UNI Pt10 FK->wp_terms.term_id
taxonomyvarchar(32) UNI Pt2  
descriptionlongtext    
parentbigint(20) unsigned  0 
countbigint(20)  0 

Indexes

KeynameTypeCardinality Field
PRIMARYPRIMARY2term_taxonomy_id
term_id_taxonomyUNIQUE2term_id
taxonomy
taxonomyINDEXNonetaxonomy

Table: wp_usermeta

FieldTypeNullKeyDefaultExtra
umeta_idbigint(20) unsigned PRINULLauto_increment
user_idbigint(20) unsigned  '0'FK->wp_users.ID
meta_keyvarchar(255)YesINDNULL 
meta_valuelongtextYesINDNULL 

Indexes

KeynameTypeCardinality Field
PRIMARYPRIMARY9umeta_id
user_idINDEXNoneuser_id
meta_keyINDEXNonemeta_key

Table: wp_users

FieldTypeNullKeyDefaultExtra
IDbigint(20) unsigned PRINULLauto_increment
user_loginvarchar(60) IND  
user_passvarchar(64)    
user_nicenamevarchar(50) IND  
user_emailvarchar(100)    
user_urlvarchar(100)    
user_registereddatetime  0000-00-00 00:00:00 
user_activation_keyvarchar(60)   
user_statusint(11)  0 
display_namevarchar(250)     

Indexes

KeynameTypeCardinality Field
PRIMARYPRIMARY1ID
user_login_keyINDEXNoneuser_login
user_nicenameINDEXNoneuser_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.