Turn your WordPress database into an ally (and not a liability)

What’s as important as water, sunlight, your main squeeze, coffee in the morning, or your Internet connection?

Answer: Your WordPress site’s database. If there’s one thing you need to pamper, coddle, nurture, and look after, it’s your WordPress database.

In fact, it’s actually the most important element of your site, as it allows your site to function.

It is also the element that screws up everything when it stops working (yes, it happens).

If you’ve ever struggled with a database connection error, you know what I’m talking about: The blank screen, the sweat, etc.

Since our DB — as we say in the jargon — is of the capricious kind, it’s not a bad idea to know the best practices to tame it.

That’s the purpose of this new article, which will introduce you to a dozen plugins and lots of concrete tips to create, manage, optimize, secure, and repair your WordPress database like a pro.

Originally written in October 2019, this article was last updated in July 2022.

Your best WordPress projects need the best host!

WPMarmite recommends Bluehost: great performance, great support. All you need for a great start.

Try Bluehost

What is a database?

To start off, let’s look at a global definition of a database. According to Wikipedia, it is “an organized collection of data stored and accessed electronically.“

More specifically, in order to understand what a database is on WordPress, let’s start from the base (no pun intended).

You should know that WP is composed of two main elements:

• On the one hand, we have the “Core” files. These are the files you find in the wordpress folder after you download it. Most subfolders begin with the prefix wp-.

• On the other hand, we have the database, which you are asked to create when you install WordPress. To function, this database uses a relational database management system called MySQL. This database is responsible for storing the information on your site. To display this information to your visitors, WordPress then executes what are called database queries (or SQL queries), which are interpreted using the PHP language on which WordPress runs.

Why is this database so important?

Simply because this is where you will find ALL the contents of your site.

To find:

• Your posts
• Your pages
• Your plugin settings
• Comments
• Revisions
• Site options
• Etc.

In other words, if your database disappears (e.g., in case of a hack or a bug on your hosting server), you lose everything. Everything, everything, everything. Absolutely everything.

…Unless you took the initiative to save it (I will show you how to do this later).

As you can see, the WordPress database is the engine of your site. If it breaks down, you are left on the side of the road.

To understand how this whole mechanism works, let’s go to the tables.

Are you curious? I’ll explain below.

How does a database work on WordPress?

Information stored in tables

Tables, tables, and more tables: That’s what a WordPress database is all about.

What exactly is a table? To illustrate, let’s go back to high school, if you will.

Back then, in my school, there was a space dedicated to storage, with dozens and dozens of lockers for the students.

Let’s say the overall structure of the lockers represents your WordPress database, and each individual locker is a table.

Overview of the tables in a database

Each table contains information about the content of your site. For example, the wp_comments table will store all the comments on your site.

You should know that by default after its installation your database contains 12 tables.

So grab a chair, sit down at the table, and I’ll do the introductions:

• wp_commentmeta: This table stores the metadata of the comments (metadata is data that accompanies the comments: author, author’s email, time of publication, and post to which it was added).

The example above, as well as those below, are from phpMyAdmin, a web application to manage your DB, available on your cPanel (an online manager that administers the whole server on many hosting providers).

• wp_comments: This table stores the comments.

• wp_links: This table stores lists of links to blogs, but it’s not used anymore (no screen capture to show you because it returns an empty result ?).

• wp_options: This table, one of the most important, contains all the site options contained in the Settings menu of your WordPress administration. I will go into further detail below.

• wp_postmeta: This table stores the metadata of posts, pages, and custom post types.

• wp_posts: This table contains all your publications (posts, pages, and custom post types).

• wp_termmeta: This table allows you to store additional data for each taxonomy term.

• wp_terms: This table contains all the taxonomies of your site (categories, tags, custom taxonomies, etc.).

• wp_term_relationships: This table contains the relationships between content types and taxonomies (i.e. which taxonomies apply to which publications).

• wp_term_taxonomy: This table stores the taxonomy of each term, which is important because a term can be present in several taxonomies (e.g. in a category and a custom post type).

• wp_usermeta: This table stores all the metadata related to the users.

• wp_users: This table contains all the users of the site, whatever their role (administrator, subscriber, editor, etc.).

For an even more detailed overview, I suggest you read this article on the WP Migrate blog.

By default, these tables all start with the prefix wp_, but your site’s tables may have a different prefix, if you have modified them. This is a good practice that we will detail at the end of this article.

What data can be found in a table?

You may have started to see this in the screenshots above: Each table takes the form of an Excel table with a variable number of columns.

Each row corresponds to a record in the database.

For example, in the wp_options table of my site (screenshot above), we can find:

• The URL (siteurl in the option_name column), i.e. the website address, on line 1
• The URL of the homepage (home) on line 2
• The name of the blog (blogname) on line 3
• The administrator’s email (admin_email) on line 6, etc.

WordPress uses these options to operate, display content, etc. Sometimes this data can be modified, and sometimes WP manages it on its own.

4 key points to know

Before moving on, let me give you four more notes about the DB:

• The 12 tables developed above are part of the “Core” of your database. This means that they will be present by default on each new DB that you create.
• To store their data, the plugins you use can use the default tables of your DB (for example, the wp_options table). However, some plugins create their own tables. This is the case for Yoast SEO and WooCommerce, for example.

• You can find the connection information for your WordPress database (DB name, user, password, DB address, etc.) in your wp-config.php file.

• The database of a multisite network database has some specificities. Each site has separate tables in the database; 9 of the 12 default WordPress tables are duplicated for each site.

Are you hanging in there? Now you’ll get your hands dirty, as they say. It’s time to learn how to create your database.

Let’s get to work!

How to create a database quickly and easily

How to create a database with cPanel

For a beginner, the easiest way to create a database on a site ready to be put online is to use cPanel, provided that your host offers it.

For example, Bluehost offers cPanel.

Among the many features of cPanel, there is a tool called Softaculous, which supports the easy installation of CMS (WordPress, for example).

Good news for you: no need to break your head with database creation anymore.

Softaculous will take care of everything for you (if you are with another host, like OVH, you can also install your site with a 1-click module).

Not bad, right? Now, if you want to try the manual method, no problem. I detail the steps below.

cPanel also allows you to create and manage databases, via the “MySQL Databases” link.

The process is pretty quick. You’ll have to give a name to your database and create an associated user or grant privileges to a user (i.e. grant the right to use the database).

For directions to create a database, I detail everything in the article How to install WordPress: A step-by-step guide.

How to create a local WordPress database

Ah, local. Nothing to do with what you eat or a place to park your bike and your furniture.

Using WordPress locally means that you will store it on your computer, without having to use the services of a hosting company.

This can be useful if you want to create or redesign a site in peace, for example.

To design a site — and thus a database — locally, there are two major options (warning, it’s useless to attempt creating a database by yourself).

Option 1: Use the Local software

The first one is called Local. This software will do almost everything for you, including the creation of your DB.

Option 2: Using MAMP

The second option available to you is MAMP. MAMP will allow you to install WordPress on Mac and Windows.

To create a database with MAMP, you’ll have to use an old friend: phpMyAdmin. Yes, him again.

Don’t panic; it’s very simple. I explain how to do it in this article.

Congratulations, your database is now created.

But your job doesn’t end here. For your database to work to the fullness of its capabilities, you need to optimize it.

I’ll tell you why and how in the next part.

Join the WPMarmite subscribers

Get the last WPMarmite posts (and also exclusive resources).

SUBSCRIBE NOW

How to manage and optimize a database with WordPress

A must: Stay slim.

It’s a natural phenomenon for human beings: As we age, we tend to put on a few superfluous pounds.

The same goes for your WordPress site. The older it gets, the more weight its database will gain as it accumulates information (e.g. publications, revisions, options, etc.).

The problem is, if the size of your WordPress database increases too much, the loading speed of your site’s pages could slow down.

As users, we’re all too familiar with a page that takes ages to load. Everyone hates that!

Moreover, even if the impact on the ranking of a page remains minor, you should know that Google’s algorithm takes into account the loading speed of a page when evaluating the user experience, using “Core Web Vitals.”

So, you don’t really have a choice: you have to optimize your database to stay on top.

At the same time, this will allow you to free up storage space on your hosting server, so as not to impair its performance too much.

Without further ado, I’ll give you a few tips on how to put your database on a diet and limit its weight gain for new sites.

Delete revisions

WordPress has a revision system that allows you to store a backup of drafts and updates of your posts in your database.

On paper, this is still a useful feature if you want to backtrack. However, it overloads your database unnecessarily, because WordPress does not limit the number of revisions.

Fortunately, a few lines of code allow you to overcome this problem.

To reduce the number of revisions for each article (the number is the number of revisions kept in the DB per content type), use the snippet below in your wp-config.php file:

define( 'WP_POST_REVISIONS', 3 );

It’s possible to go even further by disabling revisions entirely (in this case, do not use both pieces of code at the same time).

define( 'WP_POST_REVISIONS', false );

Be careful with this last piece of code. If you choose to use it, no more revisions will be saved. In the case of a mistake on your part (e.g. closing the editing window), or a bug in your browser, you will lose all the content you have created before. As a last resort, you can restore your content thanks to the automatic backup.

Clean up your database

WP Rocket, for a complete cleaning

If manipulating code doesn’t tempt you, you can use the premium plugin WP Rocket (affiliate link).

This cache plugin (a system to speed up the loading of your pages) has an option to delete your revisions.
And, more broadly, it offers various features to clean up your database in one click.

Using WP Rocket, you can purge:

• Revisions and drafts
• Comments
• Transients, which allow you to temporarily cache information in your database
• Database tables

In addition, you can schedule an automatic cleaning of your DB at the frequency of your choice (daily, weekly, monthly).

Speed up your website with WP Rocket

Turn your site into a rocket with the most powerful caching plugin recognized by WordPress experts.

Try WP Rocket

Some additional plugins to optimize your database

Speaking of plugins, here are four — free this time — that will also allow you to optimize your database (only activate one, though):

1. Advanced Database Cleaner
2. WP-Optimize
3. Optimize Database after Deleting Revisions
4. WP-Sweep

Delete unused plugins and themes

Another lever you can activate is deleting themes and plugins you don’t use or no longer use.

There’s no need to keep plugins just to fill your Dashboard, right?

The thing is, it isn’t enough to remove all their information from your database, as they can still overload it.

As a matter of fact, some plugins leave tables lying around even after deleting them.

This allows you to you reactivate them without configuring them again, for example.

For a plugin that you are sure you don’t want to use anymore, this isn’t necessary.

To delete unused tables, you can use either of the following plugins:

• WP-DBManager
• SQL Buddy

Using phpMyAdmin

If you want to do it manually, you’ll have to use phpMyAdmin.

This manipulation is not very complex, but I advise you to use it only as a last resort, and remember to save your site beforehand.

To start, go to phpMyAdmin through your host’s interface (your cPanel, for example).

Select your database in the top left corner. All your tables should then be displayed on the right side of your screen.

Then select the table(s) of your choice, and choose “Optimize the table” (be careful not to click “Empty” or “Delete”).

Still following? Let’s go to the last tip.

Get rid of spam

They can sometimes make us smile, but mostly they’re very annoying. Do you recognize them? Spam comments, of course.

These comments are definitely unwanted. As they also overload your database, there are several good reasons to get rid of them right away.

Among the many anti-spam plugins on the market, there is one that holds up well: Akismet.

With over 5 million active installations, this is the most popular plugin on the official directory.

You’ll have it by default when you install WordPress on your admin, as it was designed by Automattic, the company that contributes the most to WordPress.

So frankly, don’t deprive yourself of it.

How to secure your database

As you have just seen, optimizing your database is essential. But that’s not all. There’s another element you can’t ignore if you want to manage a database with WordPress efficiently: its security.

In this section, I’ll show you two ways to strengthen the security of your database:

1. Saving it.
2. Changing the prefix of the tables.

How to backup your database using a plugin

I’ll detail the manual process later, but let’s start with the “plugin” method, which is more accessible if you’re not a knowledgeable WP user.

Using a plugin to back up your database will make the operation much less tedious, especially since you will be able to automate and schedule the process.

I won’t hide it from you; there are dozens and dozens of plugins to achieve your goal.

To help you, Alex suggests a selection of free plugins. Among them, he advises you to use UpdraftPlus.

This plugin definitely has a lot of advantages. It allows you to:

• Download your database in one click
• Schedule backups of your database and export them to an external service (like Google Drive)
• Schedule automatic backups
• Restore your database directly from the plugin

How to backup your database manually

In some cases, you may need to make a manual backup of your database.

For example, I’m thinking of a situation where the management of your site becomes inaccessible, even momentarily, making it impossible to backup or restore your database.

To proceed without a plugin, everything happens on phpMyAdmin (again and always).

Go to this tool, then select the database of your choice by clicking on it.

It will then appear at the top of your page. Click on “Export.”

phpMyAdmin lets you choose between two export methods, quick or custom (useful for selecting specific tables).

By default, the SQL format is already selected, so don’t touch that. Click on “Go.”

A file in SQL format will then download to your computer. You are now in possession of your database. Bravo! ?

By saving your database, you have also just exported it.
As a result, it is obviously possible to import it on the site of your choice through the “Import” tab of phpMyAdmin.

The process is more or less the same as for the export, but in reverse.

In this case, you will download the SQL file of your choice and import it into your WordPress database.

How to change the prefix of the database tables

By default when installing WordPress, the prefix wp_ is assigned to your database tables.

Security-wise, this is a problem, as it makes your site more vulnerable to SQL attacks from hackers.

To cover yourself, get into the habit of changing that famous prefix.

To do this, check out this tutorial, which details 3 different methods for changing the prefix of database tables on WordPress:

• when installing WordPress
• with a plugin
• with phpMyAdmin.

Overall, the security of your site is something you should take very seriously.

How to solve the main database errors

Before leaving you to manage your database as you wish, I want to give you some final tips that may be useful when you’re at the bottom of the pit — that is, when your database stops responding.

Unfortunately, this can happen, so you might as well be prepared.

How to fix a database connection error

You’ll see this nice — I mean, ugly — message when your site is no longer able to connect to its database. Just seeing it written, it’s already a pain in the neck.

This may be due to a problem with your hosting server. In this case, a simple refresh of your page (a small stroke of F5 or refresh) can save you.

But the cause may be deeper. It would be far too simple otherwise!

In this case, you should start by checking your DB login credentials in your wp-config.php file.

If they seem to be correct but it still doesn’t work, you can generate a new password via your cPanel.

If your database is corrupted, you will need to repair it with the following code:

define( 'WP_ALLOW_REPAIR', true );

I detail the whole process in the article The WordPress error guide: how to solve them easily.

How to reset your database

In some specific cases, you may need to reset your database.

This can be useful if you’re working on a test site, or locally. It saves you from having to reinstall WordPress, for example.

Be careful: don’t do this on a production site unless you’re sure of what you’re doing. Resetting a database means that you will lose all your content. In any case, make a backup first.

For this, you can use a specific plugin like:

• WP Reset, which allows you to reset a WordPress site without altering the files on your site
• Database Reset

Both will allow you to reset the whole database or only certain tables.

Basic recap

The WordPress database is the heart of your site, its driving force.

To make sure it works properly, I’ve shown you how to:

• Create it
• Manage it
• Optimize it
• Secure it
• Repair it

A technical problem or even a bad manipulation can happen quickly, so you might as well get into the habit of protecting yourself.

For you, how do you manage your database? Or do you ignore it so you can stay focused on your site?

Don’t hesitate to share your tips and advice with WPMarmite readers in the comments below.

Receive the next posts for free and access exclusive resources. More than 20,000 people have done it, why not you?

Register

About the author

WPMarmite Team

WPMarmite helps beginners get the best out of WordPress with in-depth tutorials and honest reviews. Meet the founder, Alex, and his team right here.