Content Types (Entities) in WordPress
There are five basic types of content (data types, entities) in WordPress out of the box:
- Taxonomies (terms)
- Custom Taxonomies - register_taxonomy()
- All entities listed above can have them.
All of this data is stored in the database, so I suggest you look at the structure of the WordPress database tables:Build In Post
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:
WordPress tables (regular installation)
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_typecolumn, which indicates whether a row is a post, page, attachment, menu item, or other type.
$wpdb->poststable. 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.
- Table with the data on registered users.
- Additional information about users, such as Name, Nickname, permissions, etc.
Meta-data for $wpdb->users table.
- A table with records of comments.
- Meta fields for the $wpdb->comments table.
- A table containing basic information about each element of the taxonomy.
- A table containing additional fields for the $wpdb->terms table.
- Table with information about taxonomies, their description. The data in the
wp_termstable are not yet taxonomies - this table extends the data of each term and assigns to each term the taxonomy to which it belongs.
- A table linking taxonomies to content (posts, entries, etc.). Each row of this table defines a relationship between a post (object) in
wp_postsand a taxonomy term from the
- Table of options (settings).
- Table with link. Obsolete table, not used for a long time, but the functionality is still working, but it should be enabled separately.
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).
- Stores data of all sub-sites in the network.
- 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.
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.
Contains data when a network blog was registered.
This table records users who register a new site after its activation.
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.
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.
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).
- 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.
- 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
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
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
Ö = OEin 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.
- MySQL vs MariaDB differences: https://kinsta.com/blog/mariadb-vs-mysql/
- Useful article about WordPress database tables: https://deliciousbrains.com/tour-wordpress-database/