WordPress database structure and features

 WordPress is one of the most used CMS worldwide. However, while many users use WordPress, they often do not know exactly about the underlying database structure. Understanding the overall WordPress database structure can be an important key to optimizing site performance and solving various problems.

In this article, we will look at key parts of the WordPress database structure and how to optimize it.

1. WordPress database structure (main tables)

1.1. wp_posts

• Overview : This table is the most central table in the WordPress database structure and stores information about all posts and pages published on the site.
• Key columns :
  • ID : A unique number for each post.
  • post_title : Indicates the title of the post.
  • post_content : Saves the body content of the post.
  • post_type : Distinguishes the type of post (post, page, attachment, etc.).
• Function : Stores information on all posts and pages created, modified, or deleted by users, and provides corresponding content on the front end of the website.

1.2. wp_comments

• Overview : This WordPress database structure table stores information about comments that website visitors leave on posts or pages.
• Key columns :
  • comment_ID : A unique number for each comment.
  • comment_post_ID : Indicates the ID of the post where the comment was written.
  • comment_author : The name of the comment author.
  • comment_content : Saves the content of the comment.
• Function : A comment function is provided for communication between users, and administrators can manage comments through this table.

1.3. wp_users

• Overview : This is a table that stores data related to user information, such as website administrators, creators, and subscribers.
• Key columns :
  • ID : A unique number for each user.
  • user_login : The user's login ID.
  • user_pass : The user's encrypted password.
  • user_email : Stores the user's email address.
• Function : Manages the information of all users logging into the website and performs functions such as permission settings and user management.

1.4. wp_term_taxonomy and wp_term_relationships

• Overview : Two tables that store categories, tags, custom taxonomy information and their relationships.
• Key columns :
  • term_id : A unique number for each item (category, tag).
  • taxonomy : Indicates the type of item (category, tag, etc.).
  • object_id : Stores the ID of the related post.
• Function : Provides post classification and tagging functions in WordPress, allowing users to easily find content.

1.5. wp_options

• Overview : Saves optional data such as WordPress default settings, themes, and plugin-related settings.
• Key columns :
  • option_id : Unique number for each option.
  • option_name : Indicates the name of the setting.
  • option_value : The value of the corresponding setting.
• Function : Stores and manages information related to the overall settings of the website. This table allows users to adjust various features and designs of the site.

  2. Relationships between WordPress database structure tables

  2.1. wp_posts and wp_comments

  • Nature of relationship : Indicates the relationship between each post and the comments on that post.
  • Connection column :
    • ID of wp_posts : Unique number of the post.
    • comment_post_ID in wp_comments : Indicates the ID of the post with a comment.
  • Description : When a user leaves a comment on a specific post on a WordPress site, the columns wp_commentsin the table are linked by referencing the post. This allows you to figure out which comment belongs to which post.comment_post_IDID

  2.2. wp_users and wp_posts

  • Nature of relationship : Indicates the relationship between the author of the article and the article.
  • Connection column :
    • ID of wp_users : The user's unique number.
    • post_author in wp_posts : The author ID of the post.
  • Description : When a user writes a post in WordPress, the column of the post refers to post_authorthe user who wrote the post . IDThrough this relationship, you can find out which user wrote which article.

  2.3. wp_posts and wp_term_relationships

  • Nature of relationship : Indicates the relationship between a post and its category or tag.
  • Connection column :
    • ID of wp_posts : Unique number of the post.
    • object_id in wp_term_relationships : Indicates the ID of the related post.
  • Description : When a post belongs to a specific category or tag, the columns wp_term_relationshipsin the table are linked by referencing the post. This will help you figure out which category or tag your post belongs to.object_idID

  2.4. wp_term_taxonomy and wp_term_relationships

  • Nature of relationship in WordPress database structure : Indicates the relationship between information in a category or tag and posts belonging to that category or tag.
  • Connection column :
    • term_id in wp_term_taxonomy : Unique number for each item (category, tag).
    • term_taxonomy_id in wp_term_relationships : Indicates the unique number of the item.
  • Description : wp_term_taxonomyA table stores information about categories or tags, and wp_term_relationshipsthe table stores the relationships between these items and posts. When an article belongs to a specific item, that item term_idis wp_term_relationshipsreferenced term_taxonomy_idand linked through .

  3. How to optimize your database

  3.1. Regular database cleaning

  Over time, your WordPress database can accumulate a variety of unnecessary data (temporary data, deleted versions of posts, unused tags, etc.). If this unnecessary data continues to be stored in the database, it can grow in size and reduce performance. Therefore, it is important to clean your database regularly to remove unnecessary data.

  When cleaning your database, you can use a WordPress plugin dedicated to database optimization, such as 'WP-Optimize'. These plugins help users delete unnecessary data with just a few clicks without having to write their own SQL queries.

  Additionally, you must perform a database backup before cleaning. In case you accidentally delete necessary data, it is a good idea to familiarize yourself with backup and restore methods so that you can restore it to its original state.

  3.2. Query optimization

  Another important way to improve the performance of your database is query optimization. When using WordPress plugins or themes, some may run unoptimized queries. These inefficient queries can put a strain on your servers and slow down your website’s loading speed.

  You can utilize WordPress plugins such as 'Query Monitor' for query optimization. This plugin monitors all queries running on your WordPress site and provides information such as how long they run, what errors occur, and which queries are occurring in WordPress core, plugins, and themes.

  When you discover performance issues with a query, it is important to optimize that query. To achieve this, you can improve the query logic or add an index to improve search performance.

  3.3. Database table indexing

  Indexing is essential to speed up searches in a database. An index is a data structure that stores pointers or location information about columns in a database table, greatly improving query performance.

  In the default installation of WordPress, indexes are already set up on the main columns of the main tables. However, if new tables or columns are created while adding multiple plugins or custom fields to WordPress, you should consider adding indexes to these tables or columns.

  Be careful when adding indexes. If you add indexes indiscriminately, performance may deteriorate when entering or modifying data. Therefore, it is important to set the index appropriately only for columns that cause actual query performance problems.