This article, written to accord with WordPress 2.5, 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.5. At the time, the only database supported by WordPress Version 2.5 was MySQL version 4.0 or greater.
Also see:
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)
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.5 Tables (10)
|
| Table Name |
Description |
Relevant Area(s) of WordPress User Interface
|
| 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 Link 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.
| Field |
Type |
Null |
Key |
Default |
Extra
|
| comment_ID |
bigint(20) unsigned |
|
PRI |
NULL |
auto_increment
|
| comment_post_ID |
int(11) |
|
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) |
|
|
0 |
FK->wp_comment.ID
|
| user_id |
bigint(20) |
|
|
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) |
|
PRI |
NULL |
auto_increment
|
| link_url |
varchar(255) |
|
|
|
|
| link_name |
varchar(255) |
|
|
|
|
| link_image |
varchar(255) |
|
|
|
|
| link_target |
varchar(25) |
|
|
|
|
| link_category |
bigint(20) |
|
IND |
0 |
|
| link_description |
varchar(255) |
|
|
|
|
| link_visible |
varchar(20) |
|
IND |
Y |
|
| link_owner |
int(11) |
|
|
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) |
|
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 |
90 |
option_id
blog_id
option_name
|
| option_name |
INDEX |
None |
option_name
|
Table: wp_postmeta
| Field |
Type |
Null |
Key |
Default |
Extra
|
| meta_id |
bigint(20) |
|
PRI |
NULL |
auto_increment
|
| post_id |
bigint(20) |
|
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 |
0 |
meta_ID
|
| post_id |
INDEX |
None |
post_id
|
| meta_key |
INDEX |
None |
meta_key
|
Table: wp_posts
| Field |
Type |
Null |
Key |
Default |
Extra
|
| ID |
bigint(20) unsigned |
|
PRI & IND Pt4 |
|
auto_increment
|
| post_author |
bigint(20) |
|
|
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_category |
int(4) |
|
|
0 |
|
| 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) |
|
|
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
|
Table: wp_terms
| Field |
Type |
Null |
Key |
Default |
Extra
|
| term_id |
bigint(20) |
|
PRI |
|
auto_increment
|
| name |
varchar(55) |
|
|
|
|
| slug |
varchar(200) |
|
UNI |
|
|
| term_group |
bigint(10) |
|
|
0 |
|
Indexes
| Keyname |
Type |
Cardinality |
Field
|
| PRIMARY |
PRIMARY |
2 |
term_ID
|
| slug |
UNIQUE |
2 |
slug
|
Table: wp_term_relationships
| Field |
Type |
Null |
Key |
Default |
Extra
|
| object_id |
bigint(20) |
|
PRI Pt1 |
0 |
|
| term_taxonomy_id |
bigint(20) |
|
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) |
|
PRI |
|
auto_increment
|
| term_id |
bigint(20) |
|
UNI Pt1 |
0 |
FK->wp_terms.term_id
|
| taxonomy |
varchar(32) |
|
UNI Pt2 |
|
|
| description |
longtext |
|
|
|
|
| parent |
bigint(20) |
|
|
0 |
|
| count |
bigint(20) |
|
|
0 |
|
Indexes
| Keyname |
Type |
Cardinality |
Field
|
| PRIMARY |
PRIMARY |
2 |
term_taxonomy_id
|
| term_id_taxonomy |
UNIQUE |
2 |
term_id
taxonomy
|
Table: wp_usermeta
| Field |
Type |
Null |
Key |
Default |
Extra
|
| umeta_id |
bigint(20) |
|
PRI |
NULL |
auto_increment
|
| user_id |
bigint(20) |
|
|
'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 |
4 |
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
