Creative daily

Learn how to master WordPress Database Optimization on - (r)

Apr 17, 2024
WordPress database optimization at

-sidebar-toc>

While all of the components of a WordPress website are important, your database is your most important. The database is where the majority data on your website is kept, and can be accessed. This means that your WordPress method of optimizing your data performance must be flawless.

When your databases are afflicted by performance and bloat issues, this could slow your website and negatively impact the results of your searches. Furthermore, you may influence the overall UX (UX) also. It could affect your traffic figures and also trickle into your placements in search results (among many other problems).

Knowing WordPress Database Structure WordPress Database Structure

In essence, the WordPress database functions as the basis of your site. It hosts nearly everything such as the content, user information configurations as well as user information, and much more. With regard to WordPress tables, the database are responsible of storing particular kinds of information.

The phpMyAdmin interface on a hosting service, showing a list of WordPress database tables with options for actions like browse, structure, search, insert, and drop. The tables shown include wp_commentmeta, wp_comments, wp_links, wp_options, wp_postmeta, wp_posts, wp_termmeta, wp_terms, wp_term_relationships, wp_term_taxonomy, wp_usermeta, and wp_users.
Connecting to the WordPress database using phpMyAdmin.

As an example, post_wp_posts table includes your articles and pages, while users provides information about users of your website. We'll take a look at a few of these tables more in depth in the near future.

After installation, WordPress creates a set of default tables to cover the majority of scenarios you'll need. However, themes, plugins and installations can also generate tables that can be used to hold specific and related data.

Modular design is beneficial in numerous ways, as it allows for extensive functionality. However, there's a lot of superfluous tables (along with management issues) can lead to over the amount of bloat.

The structure of the database that WordPress uses is important to comprehend. WordPress is vital due to two factors:

  • Performance. Good database organization lets you access information quicker and also have an immediate effects on loading time as well as speed.
  • Maintenance. Knowing which tables relate to different components of your site may help in the course of maintenance. In the case of an individual plugin is creating problems, it is possible to troubleshoot its related tables.

A regular cleaning and optimizing of databases' tables can keep them from becoming big and cumbersome. The effects of performance can affect users in a negative way.

Tables specifically designed for WordPress

Although we can't go through all tables here, we'll jump into specific tables more heavily than other tables:

  • wp_postmeta. Metadata for your blog post is saved here. If your site expands and expands, it could be one of the biggest databases that you have in your database.

Take note that any themes or plugins you include to your site may modify the layout of your site also. It's uncommon to have the theme or plugin take away one of these tables, but it is possible.

It is crucial to periodically review and analyze any potential adjustments. It is essential to maintain an effective database that improves and doesn't hinder your website's performance.

The main reason WordPress Database Optimization is Necessary for Most Sites

To put it in perspective, there are two reasons that regular WordPress optimization of data is a routine requirement for workflow:

  • Improves the user experience. Users also expect an effortless and speedy browsing experience. The optimized database results in faster page loading and faster data processing. Simply put, every on-site user interaction is dependent on queries to the database. More efficient means improved UX.

In addition, as your website grows as does the database. Even though you may possess a modest but manageable setup initially, it becomes more complicated quickly. With regular maintenance it is possible to ensure that your database has the capacity to cope with the ever-growing demands of a website without compromising efficiency.

Simply put, WordPress database optimization will increase response times. If your website takes a long time to load, a mere few seconds can lead to increased bounce rates and lost traffic, which doesn't spell the greatest for your rank in the search engines.

How do you best to perform regular Maintenance and Cleaning

If you clean and regularly maintain your WordPress database, then you've got the ideal method to ensure that your website is running as smoothly as possible. It is a fact that database storage gets filled with data that is not needed over time, so the need for regular and consistent procedure is vital.

Additionally, it's important to utilize all devices and services accessible in the most efficient way possible. It's important to be aware that WordPress has a range of choices to keep your database.

A plugin can provide a number of ways to improve the performance of your database. WP-Optimize is one of the most popular solutions for the job. Although there are many alternatives, this one is rated highly and user reviews on WordPress.org, is free, and gets regularly updated updates.

The WordPress.org header image for the WP-Optimize plugin with the slogan 'Make your site fast and efficient.' The graphic shows a red motorcycle racing against a car and a jet.
The WP-Optimize header comes from WordPress.org.

In the next segments, we'll examine this in greater details and also discuss how to do it manually and using WP-Optimize. We'll cover 's own tools in the future. Before that, let's discuss certain pre-optimization methods.

Things to Do Prior To You Tackle Optimization

You should also take down any plugins or themes you don't use for your website. It could resolve a number of problems, and not only with your database. This can also help to strengthen the security of your site as well.

The WordPress dashboard showing a list of installed plugins. Each plugin has a description, with options to activate, deactivate, or delete, and settings for automatic updates.
The screen of plugins in WordPress

Be aware, however, that, based on the theme or plugin the plugin or theme, they can leave unwanted tables behind. Naturally, this is precisely why we want to improve the performance of our database by understanding for why themes or plugins allow some data in the interim can be helpful in the future.

The most important task is something you'll find when you login to your preferred database management program of your choice. There are many indicators of problems with performance Therefore, they must be addressed prior to the implementation of more improvement.

In essence, the procedure is to select each table. After that, click the Check button of your table button to generate reports.

The phpMyAdmin tool showing the 'Check table' option. Visible on the left are various table names like wp_termmeta and wp_users, each with action icons for tasks such as browsing and searching. To the right, a context menu shows options for table operations like exporting and optimizing.
Choosing the Check table option from the phpMyAdmin.

If you find errors that are not there, then this is a positive sign. There are always issues to resolve before proceeding. In this case, a call by contacting support is a great alternative.

1. Improve Your Database Tables Optimized

The first step is to improve the structure of tables within your database. With a manual approach go to the database Link within the control tool and then choose the database you prefer.

The phpMyAdmin interface showing the Databases tab. Two databases are listed, and both have check privileges actions available.
The choice of a database is done within phpMyAdmin.

Most likely, you'll already be on the database page of your WordPress website. Whatever the case it is possible to view a table list within your database. Simply bulk select them all and select one. Optimize Table selection from the drop-down menu before you click to move on.:

A context menu in phpMyAdmin for a selected list of WordPress database tables, with the 'Optimize table' option highlighted. Other options include copy table, show create, export, empty, drop, and table maintenance actions.
The option to optimize all tables of databases using the use of phpMyAdmin.

When you've been around for a while it will be possible to see an overview of the current status of each table in your database.

The phpMyAdmin interface displaying a message indicating successful SQL query execution for optimizing WordPress database tables. Below the message, individual tables are listed with notes on their optimization support status.
The report on optimization is available in phpMyAdmin.

Within the settings of WP-Optimize, select WP-Optimize > Database > Optimizations. You can then click on the Run Optimization button that is right next to the Optimize Database Tables tab:

A section of the WP-Optimize plugin interface in the WordPress dashboard, showing the 'Optimize database tables' option selected with a 'Run optimization' button highlighted. The interface indicates that the optimization will affect 159 tables.
The Run optimizations button within WP-Optimize's dashboard.

The software will process every table and give you a success signal. Once you have this it is possible to make changes.

2. Cleaning up Post-Revisions after Revisions and cleaning up

DELETE FROM wp_posts WHERE post_type = revision;

The deletion is going to remove all kinds of revision posts from the table. There's also associated data in other tables too. To remove and capture the entire array, it's possible to use the below SQL commands:

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);

Pro-tip: When wrangling database tables with many rows (particularly when joining more complicated joins, such as the ones mentioned earlier) the long-running query may be unable to finish. For MySQL, MariaDB and PostgreSQL you can use the LIMIT clause to split your work into smaller tasks:

EXCLUDATE FROM WP_postmeta IF post_id is NOT IN (SELECT ID from wp_posts) Maximum 10000

That SQL can be repeated, removing as many as 10,000 entries at a moment in this instance until the table is clean.

Also it is essential to select the right table prefix for your database when you copy-paste the following SQL examples.

With a plugin this process takes only a few seconds. Similar to all of WordPress optimizing your database The plugin WP-Optimize gives you a one-click button from inside WordPress:

The WP-Optimize plugin dashboard with a focus on database optimization. Options include cleaning all post revisions and auto-draft posts, with a blue 'Run optimization' button next to each task.
Drafts can be cleared or revise them, as well as auto-drafts can be found in WP-Optimize.
define( "WP_POST_REVISIONS' );

This is this, is the number of revisions that you'd like to retain. You could also specify the false in the field above, however we wouldn't advise this option. It is always advisable to have at least one revision that you are able to fall back on in the event that you require this.

3. Take out Spam Commentaries and other items that have been truncated

You can also use SQL queries to eliminate of comments that are spammy. When you moderate comments, unwanted ones stay in your database for 30 days. This means comments marked as spam in that time frame will sit within your database.

They are cleared with just one line of SQL in the software that manages databases:

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';

The same as things you transfer to the trash bin in WordPress. There may be a lot of content lying in the limbo' that you could remove using an additional 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';

Similar to post revisions, you can establish limits for the amount of time needed to remove junk objects from wp-config.php:

define( "EMPTY_TRASH_DAYS' )

In WP-Optimize there are three ways to help get rid of spam comments and WordPress junk:

A section of the WP-Optimize plugin in the WordPress dashboard showing various cleanup options such as removing spam and trashed comments, unapproved comments, and expired transient options, with checkboxes to select each optimization task.
It is possible to delete spam comments, spam or comments that have not been approved in WP-Optimize.

There is also the option to remove unapproved comments. This could be helpful for some situations, but we would not recommend it. Be sure to moderate your remarks before removing them if you need to.

4. Remove Unused Tags

Taxonomies are important for WordPress however they can grow into an enormous database over the time. This is an excellent application for maximizing your database. Along like other methods making use of SQL queries, you are able to utilize the following SQL query:

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;

The most efficient way to get rid of tags that aren't utilized in WP-Optimize is clean post meta data. This assesses whether you have any metadata that is missing, then you can eliminate it.

The WP-Optimize plugin interface displaying options for cleaning up the WordPress database, including removing pingbacks and trackbacks, cleaning post meta data, and cleaning user meta data with respective counts for each.
There are options in WP-Optimize that allow you to clear out orphaned post metadata.

This option could remove the categories or other information as well, it's an extremely secure choice to pick, particularly in the event there is no other piece of content or website uses them.

5. Remove Trackbacks and Pingbacks.

The WordPress dashboard’s 'Discussion Settings' screen showing checkboxes for default post settings such as notifying linked blogs, allowing link notifications from other blogs, and allowing people to submit comments on new posts.
The Discussion Settings screen in WordPress shows the pingback and trackbacks choices.

In within the Normal Post Settings space, ensure you untick the "Attempt to notify blogs of any updates" ..." and "Allow link notifications ..." options. Make sure to save the modifications. Then, go back to the tool you use for managing databases and execute the query:

CELEBRATE c FROM WP_COMMENTS cLEFT JOIN WP_COMMENTMETA cm ON c.comment_ID = cm.comment_id WHERE c.comment_type is specified ('trackback'or "pingback');

Both are at the same time, as comments. The kind is distinct and the subject matter of the question is. The plugin WP-Optimize offers two options to choose from in each case:

The WordPress dashboard displaying the WP-Optimize plugin settings with options to remove expired transient options, pingbacks, trackbacks, and to clean post meta data, showing counts of items found for each category.
The options offered by WP-Optimize to eliminate trackbacks and pingbacks.

Once you've finished this process You shouldn't see trackbacks or pingbacks inside your database for quite a long time!

Making use of Automated WordPress Database Optimization

APM cleans your database by getting rid of unneeded data like the information about transients, abandoned orphans, as well in spam remarks. In this way you'll assure that your database functions at its peak without the involvement of.

The APM dashboard featuring the 'Overall transaction time' chart, which breaks down the response time into PHP, MySQL, and external services over a specified period. The section below lists 'Slowest transactions' with metrics like total duration and average duration.
It is the APM tool.
The APM interface displaying a chart titled 'Slowest database queries', which lists various WordPress options-related SQL operations such as SELECT, UPDATE, INSERT, SHOW, and DELETE, along with their total duration percentages, total duration in milliseconds, maximum duration, average duration, and rate per minute.
A study by APM which shows the most expensive query in the database.

Access to APM through the My Dashboard Particularly, the APM screen. It is possible that you must enable it before using it.

A hosting service dashboard displaying the Application Performance Monitoring (APM) section. It includes a brief explanation of the APM feature, along with warnings about its usage. An 'Enable APM' button is prominently displayed for the user to activate the service.
You can enable APM on My Dashboard. My dashboard.

In this case the client must allow APM enough time to gather details. When it's ready, you'll be able to examine the queries that may need more enhancement.

How To Optimize Database Queries for greater Efficiency

Ensure that you optimize databases that are are used on your site is essential in boosting efficiency and speed on your WordPress website. Faster queries lead to faster access to data, which results in a speedier loading time as well as an enhanced user experience.

If you're employing queries for WordPress databases optimization here are some suggestions on how to improve their efficiency:

  • Optimize the structure of your query. You'll notice that we do not use wildcards (or the asterisks) in our example queries. Instead of using the select *, be particular about what columns you'll need. In addition, you must use join as an alternative to subqueries whenever it's possible. Subqueries can be less efficient particularly if they do not have a structured arrangement or have large data sets.
  • Make use of the query cache. Tools such as Redis are able to save the outcomes of queries into memory. The results from the query are served by the cache instead of running the query every time.

They're not exactly clear guidelines, however there's lots more that can be done. The next time we'll look at them.

Advanced WordPress Techniques for Optimizing Databases as well as Troubleshooting

'Indexing' can help you add a quick reference guide to your database. This will help your database server to locate information faster, without having to scan every table row.

To do this, determine the columns that are most frequently used within your queries. Then think about adding indexes. It is possible to do this inside phpMyAdmin (or equivalent). First, click on the table that you want to index. Then, click the tab called structure. tab:

The 'Structure' tab in phpMyAdmin for the 'wp_posts' table of a WordPress database. It displays a list of columns with types, collation, attributes, and actions available like change and drop.
The Structure tab can be accessed to search for tables in PHPMyAdmin.

Select the columns that you would want to index, and then click to choose to select the Index option near the bottom of the table.

A close-up of a section in phpMyAdmin showing the index option highlighted for a WordPress database table. Other options such as browse, change, drop, primary, unique, spatial, and fulltext are also visible.
The option to index columns is available within phpMyAdmin.

After saving your changes, the modifications will be listed in these columns.

It's important to know this "EXPLAIN statement can assist you in understanding the process by which MySQL executes the query. This can help you to identify the shortcomings and help you understand how your query interacts with indexes. In order to run it, you must insert the following statement at the top of a query that you have already. After you have run the query, SQL will break down how it will execute the query:

The phpMyAdmin SQL query output window showing a successful SQL query execution message. Below the message is an SQL command to explain a delete operation on WordPress comment tables, with details of the query execution plan.
The result of the query are accompanied by the EXPLAIN statement.

There isn't enough information on the Explain statement, however MySQL's documentation has a lot of information. MySQL documentation contains almost all information you need to be aware of.

Monitoring Performance

The monitoring of the efficiency of your WordPress database is a vital element of operating a website. It will help you spot potential issues before they becoming serious and help ensure that your website remains efficient and functional.

The phpMyAdmin 'Status' tab showing a pie chart and a list of SQL statement operations along with their frequency. The chart indicates the distribution of operations like 'set option,' 'show variables,' and 'select' since the server startup.
The phpmyAdmin status screen.

You can view the processing time of the processes and queries (on the Statistics for queries tab) and helps determine which queries require enhancement. If you want to monitor more advanced aspects of your query MySQL Workbench could be a great tool:

A detailed view of MySQL Workbench showing two SQL queries in the editor and the results pane below. The left sidebar lists database schemas, and the toolbar at the top provides various functions for database management. A snippet box on the right side offers SQL syntax help.
The MySQL Workbench principal user interface.

It offers modern features for database design, development and administration. The program also offers performance reports and diagnostics that help you optimize your database.

The WordPress.org header for the Query Monitor plugin. It shows queries by component with a focus on components like 'wordpress-seo' and 'woocommerce'. HTTP requests are listed with methods, URLs, and statuses, along with a panel for warnings and notices.
It is the Query Monitor plugin.

How Do I Handle Large Databases inside WordPress

If you have a WordPress site expands in its numbers of users, the content and users, the database naturally expands. The database can be huge and that will be encountered (or have to worry about) typically when running a website.

A majority of the suggestions that we offer in this post is suitable for massive database, or decrease the size. However, there are a number of other tips we can give for those databases which tend to be more complex than standard.

  • Archive information from the past. Instead of keeping your database up to date, you might want to consider archived posts or information from users that you do not frequently access.
  • Make use of a Use a Content Delivery Network (CDN). Serving and offloading static files like videos, images and pictures and downloading from a different server can lower the load of your website, and also help to improve speed. Additionally, your site can load faster to visitors no matter their whereabouts.
  • personal request. If you're a WordPress designer, create efficient queries to your themes and plugins. This will let you locate only the information you're looking for, keeping the efficiency of your site up.
The hosting dashboard displaying 'Site Information' with sections for basic details, environment details, and SFTP/SSH information. Graphs for site visits and overall transaction time are visible, providing analytics on website performance.
The WordPress hosting website.

Summary

Your WordPress database functions like the engine of the car, in that it is not as well-oiled as you'd like. If you don't WordPress optimizing your database could see dropping from first place to last position in the search engine rankings. Your users will also notice that your site is turning into a no-go and a well-maintained database running at a high speed is crucial to website's success.

We'd love to hear from you if our WordPress techniques for optimizing your database work for you. Let us know which had the most impact by commenting below!

Jeremy Holcombe

Content & Marketing Editor WordPress Web Developer as well as Content writer. Outside of everything WordPress I enjoy the beach, golf, along with films. In addition, I'm tall. issues (see below ;).

This post was first seen on here