How to master the optimization method of WordPress database

Although all the components of the WordPress site are important, the database is the key. In fact, all the information on the site is stored and accessed here. Therefore, the WordPress database optimization game must be perfect.

A database with bloated and performance problems can slow down the website and affect search rankings. In addition, it may affect the overall user experience (UX). This, in turn, affects your traffic data and your ranking in search results (and other issues).

in this tutorial, we will optimize the WordPress database from multiple perspectives. We will discuss the general structure you will see, as well as many optimization techniques that can be used. We will also mention some tools and services that can help you keep your database running.

how to understand WordPress database structure

in short, WordPress databases are the backbone of the website. It contains almost all content, user data, settings, and so on. With regard to WordPress, the database Table is responsible for storing specific types of information.

this platform uses a MySQL or MariaDB database management system, and you can use tools such as phpmyAdmin or Adminer to access the database:

uses phpMyAdmin to access the WordPress database

for example, thewp_poststable contains posts and pages, whilewp_usersholds the data of site users. We will describe some of these tables in more detail later. When

is installed, WordPress creates a set of default tables to cover all typical usage cases. However, plug-ins, themes, and installations can also create tables to store specific related data.

‘s modular approach is excellent in many ways because it implements a wide range of functions. On the other hand, too many forms (and poor management) can also lead to bloating.

is critical to understanding the database structure of WordPress for two reasons:

• Performance. Good database organization allows you to retrieve data faster, which directly affects load time and performance.
• Maintain. Knowing which forms correspond to different parts of the site is helpful for site maintenance. For example, if there is a problem with a plug-in, you can troubleshoot its related tables.

regularly cleans and optimizes the tables in the database to prevent them from becoming too large and bloated. As we have pointed out, the performance impact may have a negative impact on you.

WordPress Special Table

all types of Web sites use databases, not just WordPress. The platform has its own database table types and roles, and if you want to optimize them, you must fully understand them.

although we won’t cover all of these tables here, you will have more access to some tables than others:

• wp_options. This table stores site-wide settings and can be said to be one of the most visited tables in the database. You need to keep this table concise with good optimization.
  wp_postmeta. Your post metadata is stored here. With the development of the website, this will become one of the largest tables in the database.
• wp_usersandwp_usermeta. All information related to site users and their metadata is stored in these two tables. In rare cases, if you are locked out of WordPress, you may need to change your password here.

keep in mind that every additional plug-in or theme you add to the site may modify this structure. However, it is rare for a theme or plug-in to remove one of the tables. Even so, you should review and understand these potential changes on a regular basis. This is the key to maintaining and optimizing the database, which can support rather than hinder the performance of the site.

Why do most websites need to optimize WordPress databases?

optimizing WordPress databases is not only a good practice, but also necessary for most sites that want to provide a seamless experience. Your database is a centralized storage place for all important data on the site. Its health directly affects the performance, speed and reliability of the website.

therefore, regular optimization of the WordPress database should be the main part of the workflow for two general reasons:

Improve the speed and performance of the website. Over time, your WordPress database will contain unnecessary or redundant data, such as instantaneous options, revised articles, spam comments, or outdated drafts. This kind of bloating will slow down the speed of information retrieval.

• Improve the user experience. Users also want a smooth and fast browsing experience. The optimized database can speed up the page loading speed and improve the efficiency of data processing. In short, every website interaction depends on a database query. Higher efficiency leads to a better user experience.
• also has some more advanced reasons to ensure that the database is optimized. For example, an unoptimized database puts additional pressure on the server because it needs to work harder to find and provide data. If you are using a shared host with limited resources, this can have a serious impact.

In addition, as the website grows, so will the database. Although you may be running a small, manageable system at first, its complexity increases rapidly. By tuning periodically, you can ensure that the database can handle the expansion of your Web site without affecting performance.

in short, WordPress database optimization will improve response time. Even a few seconds of load delay can lead to increased bounce rates and loss of traffic, which is not good news for your search rankings. How

regularly maintains and cleans

if you regularly clean and maintain the WordPress database, you have one of the best ways to keep your site running as smoothly as possible. However, over time, databases may be flooded with unnecessary data, so regular and consistent workflows are important.

in addition, you should make the best use of all the tools and services you have. The good news is that WordPress provides many ways to maintain databases.

a plug-in will have countless ways to optimize the database. WP-Optimize is a popular solution. There are other plug-ins, but this plug-in has high ratings and reviews on WordPress.org, is free, and is updated regularly.

We will show you how to use this plug-in to optimize the WordPress database throughout the article, but you can also port these instructions to the plug-in of your choice. The next few sections of

WP-Optimize

will cover this in detail, and we will introduce manual methods and the use of WP-Optimize. But first, let’s introduce some pre-optimization work. Preparation before

tuning

has some simple tasks to complete before entering the database. First of all, you should make a full backup of the website and database. If the worst happens and you destroy your website, you can restore it in a few minutes.

you also need to remove any plug-ins or themes that are not used on the site. This can solve some problems, not just the database problem. It also helps to enhance the security of the website. The plug-in list

in

WordPress, however, note that depending on the plug-in or theme, this may leave unwanted tables. Of course, that’s why we want to optimize the database, so knowing which plug-ins and topics leave instantaneous data will help follow up. The last task of

is to log in to the database management tool of your choice. Database errors are clearly a manifestation of performance problems, so they should be resolved before further tuning.

in short, the process is to select all the tables and then use the “Check table” button to generate the report.

selects the “checklist” option in phpMyAdmin

if you see “OK” or similar information, this is the best state. However, any errors need to be resolved before continuing. It might be a good idea to ask for support at this point.

1. The first step in optimizing database tables

is to optimize the tables in the database. When using the manual method, please visit the Databases link in the administrative tool, and then select the database:

selects the database in phpMyAdmin

in many cases, you have entered the database of the WordPress website. In any case, you will see a list of tables in the database. Just select all the tables in batches, select the “Optimize table” option from the drop-down menu, and then click “Go”:

chooses to optimize all database tables using phpMyAdmin

for a period of time, you will see a report that summarizes the status of each table in the database: the optimization report

in

phpMyAdmin uses WP-Optimize to go to WP-Optimize & gt; Database & gt; Optimizations. Here, click the run optimization button next to the optimize database table option: the run optimization button in the

WP-Optimize panel, the

plug-in will run all the tables and give you a success message. At this point, you can continue to modify the article.

2. Cleaning up the revised version of the article

WordPress’s revised article system means that every save you make increases over time. You can easily delete these contents from the database using SQL queries. The simplest way is only one line:

DELETE FROM wp_posts WHERE post_type = ‘revision’;

, which deletes all types of modified positions in the table. However, there is also relevant data in other tables. To capture and delete all this data, you can use the following code snippet:

, of course, if you copy and paste, you should use the correct table prefix for your database. With the plug-in, the job takes only a few seconds. Like general WordPress database optimization, WP-Optimize allows you to click a button in WordPress:

DELETE FROM wp_posts WHERE post_type = 'revision';
DELETE FROM wp_term_relationships WHERE object_id NOT IN (SELECT ID FROM wp_posts);
DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT ID FROM wp_posts);

clears the option for revisions and automatic drafts in WP-Optimize

in the future, you can limit the number of revisions used by WordPress by accessing the wp-config.php file. Add the following line to this file and save the changes:

here,

define( 'WP_POST_REVISIONS', X );

is the number of revisions you want to keep. You can also specify “X” here, but we do not recommend it. You always want at least one revision to be available when needed.false3. Delete spam comments and spam items

We can also use SQL queries to delete spam comments. Please note that when you review comments, unwanted comments will be kept in the database for 30 days. This means that comments marked as spam during this period will remain in the database.

you only need to type a few lines of SQL statements into the database administration tool to completely clear these comments:

DELETE FROM wp_comments, wp_commentmeta
USING wp_comments
LEFT JOIN wp_commentmeta ON wp_comments.comment_ID = wp_commentmeta.comment_id
WHERE wp_comments.comment_approved = ‘spam’;

is a similar case for items sent to trash bins in WordPress. There may be a lot of content "up in the air" that you can delete using another SQL query:

DELETE p, pm, tr
FROM wp_posts p
LEFT JOIN wp_postmeta pm ON p.ID = pm.post_id
LEFT JOIN wp_term_relationships tr ON p.ID = tr.object_id
WHERE p.post_status = ‘trash’;

like modifying articles, you can also set the time required to delete spam items in wp-config.php:

in WP-Optimize, there are three options to help you delete spam comments and WordPress trash bins:

define( 'EMPTY_TRASH_DAYS', X )

has the option to delete spam comments, trash bins and unapproved comments in WP-Optimize

and the option to delete unapproved comments. This may be useful in some cases, but we do not recommend it. Instead, you can soften these comments and then clear them if necessary.

4. Removing unused tag

classification criteria is important for WordPress, but over time they can become a large collection. This is an excellent use case for tuning databases, and like other techniques, you can use SQL queries: the most recent option for

to remove unused tags from WP-Optimize is Clean post meta data. This feature evaluates if there is any orphan metadata and deletes it: the option in

DELETE t, tt
FROM wp_terms AS t
INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id
LEFT JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE tt.taxonomy = 'post_tag' AND tt.count = 0;

WP-Optimize clears unowned article metadata

although this option may also delete categories and other data, it is a secure solution, especially if no other posts or pages are using them.

5. Delete Pingback and Trackback

We don’t know how many WordPress sites use pingback and trackback in this day and age, but if you don’t turn off the settings, they will still disrupt your database. The first task is to check that this option is disabled. This is a simple task in WordPress’s “Settings” & gt; “Discussion” screen: the pingback and trackback options of the “discussion” settings interface in

WordPress

in the “default article settings” section, be sure to uncheck the “attempt to notify the blog linked in the article …” And allow other blogs to send link notifications (Pingback and Trackback) to new posts and save changes. Next, return to the database management tool and run the following query:

DELETE c, cm
FROM wp_comments c
LEFT JOIN wp_commentmeta cm ON c.comment_ID = cm.comment_id
WHERE c.comment_type IN (‘trackback’,’ pingback’);

, although the type is different and the focus of the query is different, both are in the same place as the comment. WP-Optimize provides two options for these two types: the

WP-Optimize delete trackback and the pingback option

, and once this is done, you will no longer see trackbacks or pingback in the database!

uses WPTurbo’s WordPress database auto-tuning feature

. You can also use the developed WordPress optimization plug-in WPTurbo to achieve database optimization.

WPTurbo cleans up your database by deleting revisions, automatic drafts, Recycle Bin articles, instantaneous data, spam comments, transients, and other unnecessary data. Therefore, you do not need to enter to ensure that the database remains optimized.

WPTurbo plug-in

using the WPTurbo plug-in is not the focus of this article, but our plug-in belongs to the out-of-the-box type, webmaster can quickly get started and perform relevant optimization after installing the plug-in.

in addition, WPTurbo is not a simple database optimization plug-in, in addition to database optimization, but also can achieve WordPress slimming, speed optimization and object storage and other functions, multi-strategy combination to achieve WordPress performance optimization. How

optimizes database queries to improve efficiency

optimizing the database queries you use is critical to improving the performance and speed of WordPress sites. Efficient queries mean faster retrieval of data, which in turn improves load time and user experience.

given that you may use queries for WordPress database optimization, here are some tips on how to improve query efficiency:

Optimize the query structure. You will notice that we did not use wildcards (or asterisks) in the sample query. Instead of using

• , be specific to what you need. In addition, useSELECT *instead of subqueries whenever possible. Subqueries can be inefficient, especially if they are not well structured or involve large datasets.JOINUse query caching. Tools such as Redis can store query results in memory. This means that the query results can be provided from the cache without having to query the database next time.
• these are vague hints, but there are many things you can do. Next, let’s take a quick look at.

Advanced WordPress Database Optimization and troubleshooting techniques

indexes can help you add a quick reference guide to your database. It helps the database server find data faster without scanning each row in the table.

to do this, identify the columns that are frequently used in the query and consider adding indexes to them. You can do this in phpMyAdmin (or similar software). First, click the table to which you want to add the index, and then go to the “Structure” tab:

accesses the structure tab of the table in phpMyAdmin

next, select the column to be indexed, and then select the Index option at the bottom of the table:

selects the index column in phpMyAdmin

After you save the changes, this will index these columns. The

statement can also help you understand how MySQL executes queries. This can help you find inefficiencies and understand how your queries interact with indexes. To run this statement, add it to the front of an existing query. At run time, SQL will break down how to execute the query:

EXPLAINruns the output of the query prefixed with the EXPLAIN statement

although the MySQL document covers almost everything about the

statement, but we cannot cover them here.EXPLAINmonitoring performance

monitoring the performance of the WordPress database is an important part of the website operation. It helps identify potential problems before they escalate and ensures that the site remains efficient and responsive.

many of the techniques we introduced in this article will be used for performance monitoring, such as using

in slow queries. However, there are more ways to use it. In fact, phpmyAdmin contains its own performance metrics in the Status tab of the server: theEXPLAINphpmyAdmin status interface

, which allows you to view the execution time and progress of the query (on the Query statistics tab) to help identify queries that need to be optimized. For advanced monitoring, MySQL Workbench will be priceless:

, the main user interface of

MySQL Workbench, provides you with advanced database design, development, and management capabilities. It also provides performance reports and diagnostics to help you fine-tune the database.

in WordPress, the perennial Query Monitor plug-in allows you to monitor database queries, hooks, conditions, HTTP requests, and so on. The

Query Monitor plug-in

is particularly useful for detecting slow queries and identifying plug-ins or topics that cause slow queries. But in most cases, this is a development plug-in, so it may not be suitable for production sites. How

deals with large databases in WordPress

naturally expands with the growth of WordPress site content, users, and traffic. When running a website, you will often encounter (or worry about) large databases.

most of the recommendations we give in this article apply to large databases-and to reduce them. However, for databases that are themselves larger than the average database, there are many other suggestions:

Archive old data. Instead of making all the data real-time, consider archiving old articles or user data that are not accessed frequently.

• Use a content delivery network (CDN). Uninstalling and providing static resources, such as pictures, videos, and downloads, from another server can reduce the load on the site and help the site accelerate. In addition, the site loads faster no matter where the user is.
• Custom query. If you are a WordPress developer, write efficient queries for your topics and plug-ins. This will help you get only what you need and stay efficient.
• you can even consider using advanced technologies such as table partitioning and database sharding. This is to split the data into multiple databases. This is usually complex and usually needs to be managed by experts.

said that experts are essential to achieve high performance in your database and affiliated sites.

Summary

your WordPress database is like a car’s engine: without tuning, it won’t perform the performance you need. In fact, without optimization of the WordPress database, your search ranking may fall from the first to the last. Your users will also find your site lacklustre, so a fast top-level database is the key to success.

has many ways to do this, but combining optimization tools such as human technology and plug-ins will bring you the greatest benefits. In many cases, automation can help you accomplish many of these tasks without having to log in or run any specific tools yourself.

We’d like to know if our WordPress database optimization techniques are useful to you. Please tell us which is the most effective in the comments section below!

我们很想知道我们的 WordPress 数据库优化技巧是否对您有用。请在下面的评论区告诉我们哪个最有效！