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.

Multisite Tables

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 data of all sub-sites in the network.
wp_blogmeta
Appeared in WP 5.1. It stores service information about blogs, for example: db_version, db_last_updated. With the introduction of this table, you don't need to use the wp_options table to store some site-specific data and switch_to_blog() every time you need such data.
wp_blog_versions

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

When you update the version of the WordPress site running on your network, sometimes changes are made to the database. Updating a multi-site installation to a new WordPress version will apply these changes to the global tables. However, the update will also need to be applied to a set of tables for sub-sites in the network. This table records the database version of each blog in the network, so WordPress knows which blogs need to be updated and updates them after the update is initiated.

wp_registration_log

Contains data when a network blog was registered.

This table records users who register a new site after its activation.

wp_signups

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

This table stores data about blogs that were registered but not activated when the network allows the registration of new sites. After the site is activated, the entry is deleted and a record is created in wp_blogs.

wp_site

Contains addresses of main sites.

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

wp_sitemeta

Site data: various options, including site administrator.

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

wp_users
List of users from all network sites. This is the common user table for the entire network. It is a familiar table, with the addition of 2 fields in the multisite version: spam and delete.
wp_usermeta
Contains user meta-data. User settings for different sites in the network.
Basic tables for each network site

Network site tables: wp_posts, wp_options, etc. For each network site, identical tables are created, but with different prefixes, for example, wp_2_posts. Here, 2 is the sub-site id.

The following set of tables is created for sub-sites:

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

Storage Mechanisms in MySQL

The storage engine is a part of the database that is 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, as it has row-level locking instead of full table locking (which is crucial for performing mysqldump exports/ backups), supports transactions (allowing SQL queries to be committed and rollbacked), and has full support for foreign keys and relationship constraints.

MyISAM was a data storage engine that was previously used in WordPress, and you might still have old WordPress sites running on it. Some sites may even have a mixture 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 migrating a WordPress database from one server to another and wondered about character sets and collations mentioned in support articles. So what are character sets and collations?

MySQL character set is a set of characters allowed in a string. There are 26 characters in the alphabet, 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 character encoding.

The combination of all letters from a to z and their corresponding numeric encodings forms a character set. MySQL supports many character sets that allow storing almost any character 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 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 a host of rules, not only for distinguishing letter case, but also for distinguishing accents (an accent is a mark attached to a character, as in German Ö), as well as for matching multiple characters (for example, a rule according to which Ö = OE in one of two German collations).

MySQL allows setting character sets and collations 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 switched from the utf8 character set to utf8mb4, allowing for storing 4-byte Unicode characters, meaning that the database can store any Unicode characters. Peter Tasker wrote an excellent guide for developers on how Unicode works.