MySQL Database Structure in WordPress
A lot of new WordPress users are pretty intimidated by the MySQL database, perhaps because it seems to be way above their technical skills or abilities. Truth be told, regular WordPress users don’t really ever have to dig into the database unless they want to. You need to explore the database only if you’re dealing with theme or plugin development, or contributing code to the WordPress project.
Currently, WordPress requires MySQL version 5.0 (or greater) to work correctly. If your web-hosting provider doesn’t have 5.0 (or greater) installed on your web server, kindly ask to upgrade.
After you install WordPress on your server, the database gets populated with 11 tables that exist to store different types of data from your WordPress site. The structure of the tables is as follows:
wp_commentmeta: This table stores every comment published to your site containing information, or metadata, that includes
A unique comment ID number.
A comment meta key, meta value, and meta ID — the meta information here are unique identifiers assigned to each comment left by you, or visitors, on your site
wp_comments: This table stores the body of the comments published to your site, including
A post ID that specifies which post the comment belongs to
The comment content
The comment author’s name, URL, IP address, and e-mail address
The comment date (day, month, year, and time)
The comment status (approved, unapproved, or spam)
wp_links: This stores the name, URL, and description of all links you create using the WordPress Link Manager; it also stores all the advanced options for the links you created, if any.
wp_options: This stores all the option settings that you set for WordPress after you install it, including all theme and plugin option settings.
wp_postmeta: This includes all posts or pages published to your site and contains metadata that includes
The unique post ID number (each blog post has a unique ID number to set it apart from the others)
The post meta key, meta id, and meta value — the meta information here are unique identifiers assigned to every post on your site and any custom fields you’ve created for the post
wp_posts: This table features the body of any post or page you’ve published, including autosaved revisions and post option settings, such as
The post author, date, and time
The post title, content, and excerpt
The post status (published, draft, or private)
The post comment status (open or closed)
The post type (page, post, or custom post type)
The post comment count
wp_terms: This stores the categories you’ve created for posts and links as well as tags that have been created for your posts.
wp_term_relationships: This stores the relationships among the posts as well as the categories and tags that have been assigned to them.
wp_term_taxonomy: WordPress has three types of taxonomies by default: category, link, and tag. This table stores the taxonomy associated for the terms stored in the wp_terms table.
wp_usermeta: This table features metadata from every user with an account on your WordPress website. This metadata includes
A unique user ID
A user meta key, meta value, and meta ID — the meta information here includes unique identifiers for each user on your site
wp_users: The list of users with an account on your WordPress website is maintained within this table and includes information like
The username, first name, last name, and nickname
The user login
The user password
The user e-mail
The registration date
The user status and role (subscriber, contributor, author, editor, or administrator)
Most web-hosting providers provide you with a utility, or an interface, to view your MySQL database, and the most common one is phpMyAdmin. If you’re unsure how you can view your database on your hosting account, get in touch with your hosting provider to find out.