Content Types (Entities) in WordPress

There are five basic types of content (data types, entities) in WordPress out of the box:

All of this data is stored in the database, so I suggest you look at the structure of the WordPress database tables:

WordPress uses MySQL v5.7 or higher, OR MariaDB v10.3 or higher as the database.

All tables below have the default prefix wp_. You may have a different prefix - you can change it in the wp-config.php file, there it is specified in the variable:

$table_prefix = 'wp_';

The names of all tables are in the object wpdb and to get, for example, the name of the table wp_posts with the current prefix set in the configuration file can be as follows: $wpdb->posts.

WordPress Tables (regular installation)

Wordpress database schema - the structure of all tables

Posts

read more

wp_posts

Table where posts, static pages, custom post types, attachments, etc. are written.

The posts table is probably the most important table in the WordPress database. Its name is sometimes misleading to people who think it only stores blog posts. However, despite the unfortunate name, it is an extremely powerful table that stores various types of content including posts, pages, menu items, media attachments and any custom post types that the site uses.

The flexibility of the table is provided by the post_type column, which indicates whether a row is a post, page, attachment, menu item, or other type.

wp_postmeta

Completes $wpdb->posts table. Stores additional data of posts they are also called metadata.

This table stores any additional information about individual posts. This table uses key/value pairs to store the data. This technique is used in WordPress in a number of tables throughout the database, allowing WordPress core, plugins, and themes to store an unlimited amount of data with different keys.

Users

read more

wp_users
Table with the data on registered users.
wp_usermeta
Additional information about users, such as Name, Nickname, permissions, etc.
Meta-data for $wpdb->users table.

Comments

read more

wp_comments
A table with records of comments.
wp_commentmeta
Meta fields for the $wpdb->comments table.

Taxonomies

read more

wp_terms
A table containing basic information about each element of the taxonomy.
wp_termmeta
A table containing additional fields for the $wpdb->terms table.
wp_term_taxonomy
Table with information about taxonomies, their description. The data in the wp_terms table are not yet taxonomies - this table extends the data of each term and assigns to each term the taxonomy to which it belongs.
wp_term_relationships
A table linking taxonomies to content (posts, entries, etc.). Each row of this table defines a relationship between a post (object) in wp_posts and a taxonomy term from the wp_term_taxonomy table.

Other tables

wp_options
Table of options (settings).
wp_links
Table with link. Obsolete table, not used for a long time, but the functionality is still working, but it should be enabled separately.

WordPress Tables (multisite)

When a WordPress site is converted into a multi-site installation, a "network" of sub-sites is created. The current site becomes the first subsite of the network. In the database, this site becomes the network site (wp_site), and each subsite is labelled as a blog (wp_blogs).

wp_blogs

Stores information about each site in the WordPress Multisite network.

blog_id is the site ID, and site_id is the network ID. This is legacy WP 2.x, when 'site' was called 'blog'. Thus, when you add a new sub-site (blog), a new record will be added to the wp_blogs table with a new blog_id value and the same site_id.

Field Type Key Default Description
blog_id bigint(20) PK: blog_id Unique identifier for the blog.
site_id bigint(20) 0 Identifier of the network to which the blog belongs.
domain varchar(200) K: domain Domain name of the blog.
path varchar(100) K: domain Path of the blog relative to the domain.
registered datetime '0000-00-00 00:00:00' Date and time of blog registration.
last_updated datetime '0000-00-00 00:00:00' Date and time of the last update of the blog.
public tinyint(2) 1 Flag indicating whether the blog is public (1) or private (0).
archived tinyint(2) 0 Flag indicating whether the blog is archived (1) or not (0).
mature tinyint(2) 0 Flag indicating whether the blog contains adult content (1) or not (0).
spam tinyint(2) 0 Flag indicating whether the blog is marked as spam (1) or not (0).
deleted tinyint(2) 0 Flag indicating whether the blog is deleted (1) or not (0).
lang_id int(11) K: lang_id 0 Language identifier of the blog.
wp_blogmeta

Introduced in WP 5.1. Stores metadata related to blogs - auxiliary information about blogs. For example: db_version, db_last_updated. With the introduction of this table, you no longer need to use the wp_options table to store some data about the sub-site and use switch_to_blog() every time such data is needed.

Field Type Key Default Description
meta_id bigint(20) unsigned PK: meta_id Unique identifier for the metadata.
blog_id bigint(20) K: blog_id 0 Identifier of the blog to which the metadata relates.
meta_key varchar(255) K: meta_key NULL Metadata key.
meta_value longtext NULL Metadata value.
wp_registration_log

Logs registrations of new blogs in the network. Contains data on when the network blog was registered.
Users who register a new site after its activation are recorded in this table.

Field Type Key Default Description
ID bigint(20) PK: ID Unique identifier for the record.
email varchar(255) Email address used during registration.
IP varchar(30) K: IP IP address from which the registration was made.
blog_id bigint(20) 0 Identifier of the blog associated with the registration.
date_registered datetime '0000-00-00 00:00:00' Date and time of registration.
wp_signups

Stores information about applications for registering new users and sites.

Contains users who were registered through the basic WordPress registration from the page: Administration > Super Admin > Settings.

This table also stores data about sub-sites (blogs) that were registered but not activated. When the network allows new sites to be registered. After the site is activated, the record is deleted and a record is created in wp_blogs.

Field Type Key Default Description
signup_id bigint(20) PK: signup_id Unique identifier for the signup.
domain varchar(200) K: domain Domain name requested during registration.
path varchar(100) K: domain Path requested during registration.
title longtext Blog title specified during registration.
user_login varchar(60) K: user_login User login specified during registration.
user_email varchar(100) K: user_email User email specified during registration.
registered datetime '0000-00-00 00:00:00' Date and time of registration.
activated datetime '0000-00-00 00:00:00' Date and time of activation.
active tinyint(1) 0 Flag indicating whether the user is active (1) or not (0).
activation_key varchar(50) K: activation_key User activation key.
meta longtext Additional metadata about the registration.
wp_site

Contains information about networks (main sites of the network).

This table will always contain one network, although the structure of the table allows for multiple networks in one database. This has not been implemented in WordPress itself, but can be achieved with plugins such as WP Multi Network or Networks for WordPress.

Field Type Key Default Description
id bigint(20) PK: id Unique identifier for the site.
domain varchar(200) K: domain Domain name of the site.
path varchar(100) K: domain Path of the site relative to the domain.
wp_sitemeta

Stores metadata related to networks (sites) - network options and common options for all its sites.

This table is similar to wp_options, except that it stores options for the entire network of sites, not for a specific sub-site. It stores configuration (options) related to the network, as well as other data, such as plugin settings that should be available for the entire network (for any site in the network).

Field Type Key Default Description
meta_id bigint(20) PK: meta_id Unique identifier for the metadata.
site_id bigint(20) K: site_id 0 Identifier of the site to which the metadata relates.
meta_key varchar(255) K: meta_key NULL Metadata key.
meta_value longtext NULL Metadata value.
wp_users
A list of users from all sites in the network. This is a shared user table for the entire network. This is a familiar table, but in the multisite version, two additional fields are added: spam and delete.
wp_usermeta
Contains metadata for users. User settings for different sites in the network.
wp_blog_versions

Contains the current version of the database for each site. The data is updated when the database is updated for each site in the network.

When updating the version of WordPress that your site is running, changes to the database sometimes occur. Updating a multisite installation to a new version of WordPress will make these changes in the global tables. However, the update will also need to be applied to the set of tables for subsites in the network. This table records the version of the database for each blog in the network, so WordPress knows which blogs need updating and updates them after it starts.

Base tables of each site in the network

Network site tables: wp_posts, wp_options, etc. Identical tables are created for each site in the network, but with different prefixes, for example, wp_2_posts. Here, 2 is the subsite id.

A set of tables is created for subsites:

wp_2_options
wp_2_posts
wp_2_postmeta
wp_2_comments
wp_2_commentmeta
wp_2_terms
wp_2_termmeta
wp_2_term_relationships
wp_2_term_taxonomy
wp_2_links

Data Storage Mechanisms in MySQL

The storage engine is the part of the database responsible for reading and writing data. Starting from MySQL version 5.5, the default storage engine is InnoDB. This is the most commonly used data storage engine because it has row-level locking instead of full table locking (which is very important when performing mysqldump exports/backups), supports transactions (allowing SQL queries to be committed and rolled back), and has full support for foreign keys and relationship constraints.

MyISAM was the data storage engine that was previously used in WordPress, and you may still have old WordPress sites running on it. Some sites may even have a mix of tables using both MyISAM and InnoDB.

Tip: Convert MyISAM to InnoDB using phpMyAdmin to improve database performance.

You may have encountered character encoding issues when transferring a WordPress database from one server to another and wondered what character sets and collations are mentioned in support articles. So what are character sets and collations?

A MySQL character set is a set of characters allowed in a string. In the alphabet, there are 26 characters - from a to z. Each letter is assigned a number, for example, a = 1, b = 2, c = 3 and so on. A letter is a character, and the associated number is the encoding.

The combination of all letters from a to z and their corresponding numeric encodings makes up a character set. MySQL supports many character sets that allow virtually any character to be stored in a string.

MySQL collation is a set of rules used for comparing characters in a specific character set. To compare strings, the database uses the character encoding numbers. An example of a collation rule is case-insensitive collation, where strings are compared even if they consist of lowercase or uppercase characters. Collations can be quite complex, as described in the MySQL documentation:

most collations have multiple rules, not only for distinguishing letter case but also for distinguishing accents ("accent" is a mark attached to a character, as in the German Ö), as well as for matching multiple characters (for example, a rule that states Ö = OE in one of the two German collations).
MySQL allows character sets and collations to be set at four levels: server, database, table, and column.

For WordPress sites, the recommended character set is utf8mb4, and the recommended collation is utf8mb4_unicode_520_ci. In WordPress 4.2, tables were converted from the utf8 character set to utf8mb4, allowing for the storage of 4-byte characters, meaning that any Unicode characters can be stored in the database. Peter Tasker wrote an excellent guide for developers on how Unicode works.