What is as important as water, sunshine, the apple of your eye, morning coffee, or an Internet connection?

Answer: your WordPress website database. If there's anything to pamper, cuddle, watch over like milk on the stove, it's it.

In fact, it is simply the most important element of your site. The one that makes it work.

The one which also messes up 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 white screen, the cold sweat and the like.

Since our DB or database is the fickle type, knowing the best practices to tame it will not be too much to ask.

This is the purpose of this new article, which will present you with lots of concrete tips to create, manage, optimize, secure and repair your WordPress database like a pro.

Presenting the database and how it works on WordPress

To begin with, let's see a global definition of a database. According to the Larousse Encyclopedia, it is a “structured set of files grouping together information with certain characteristics in common”.

More specifically, to fully understand what a database on WordPress is, let's start from the base, no pun intended.

It is important to know that WP is composed of two main elements :

  • On the one hand, you have the core files. You know, these are the files you find in the wordpress folder, after you download it. Most subfolders begin with the prefix wp-.
Les fichiers du Core de WordPress
Files and folders from the WordPress Core
  • On the other, you have the database, which you are asked to create when you install WordPress. To work, this database uses a relational database management system called MySQL. The latter is responsible for storing your site's information. 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.

Why is this database so important?

Quite simply because it's inside that we find ALL the contents of your site, namely your articles, your pages, the settings of your plugins, comments, revisions, site options etc.

To put it another way, if your database disappears (e.g. in case of hacking or a bug on your hosting server), you lose everything. Absolutely everything.

Unless you've taken the lead in saving it (I'll show you how to do that later).

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

So to fully understand how all this mechanics work, I suggest you go to the tables.

Does that intrigue you? I'll explain just below.

The WordPress database: a history of tables

Tables, tables and more tables: that's what a WordPress database is all about.

What exactly is a table? For the sake of imagery, let's go back to high school, if you don't mind.

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

Un homme dans un casier
That kind of locker, but with notebooks and books in it, not contortionists.

Well, let's say the overall locker structure represents your database.

And each individual locker, one table.

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

It is important to know that by default, after its setup, your database contains 12 tables.

Un diagramme des tables de la base de données WordPress
A diagram of your DB tables from the Codex, the official WordPress documentation.

So grab a chair, sit at the table, and let me make the introductions:

  • wp_commentmeta : this table stores the metadata of the comments (metadata are data that accompany the comments, author, author's email, time of publication, and article on which it was written).
The wp_commentmeta table
This example, as well as the ones below, are from phpMyAdmin, a web application to manage your DB available on your cPanel.
  • wp_comments : this table stores the comments.
The wp_comments table
  • wp_links : this table stores lists of links to blogs, but it is no longer used (so I have no example to display, as it returns an empty result ;-)).
  • wp_options This table, one of the most important, contains all the options of the site – those contained in the Settings menu of your WordPress administration. I'll tell you more about it below.
  • wp_postmeta: this table stores metadata for articles, pages and custom post types.
The wp_postmeta of your WordPress database
  • wp_posts : this table contains all your publications (articles, pages, custom post types).
The wp_posts table of a WordPress database
  • wp_termmeta : this table allows to record additional data for each taxonomy term.
  • wp_terms : this table contains all the taxonomies of your site (categories, tags, custom taxonomies etc.).
The wp_terms table
  • wp_term_relationships : this table contains the relationships between content types and taxonomies (i.e. which taxonomies apply to which publications).
The wp_term_relationships table
  • 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).
The wp_term_taxonomy table
  • wp_usermeta : this table stores all metadata related to users.
The wp_usermeta table
  • wp_users : this table contains all the users of the site, whatever their role (administrator, subscriber, editor etc.).
The wp_users table

By default, these tables all start with the prefix wp_, but you may have a different prefix at home, if you have changed it. This comes as a good practice that I'll show you at the end of this post.

You may have started to see this through the above captures: each table is in the form of an Excel table with a variable number of columns.

Each line corresponds to a record in the database.

The wp_options table of a WordPress database

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

  • Its URL (siteurl in the option_name column), i.e. the address of the website, on line 1.
  • The URL of the home page on line 2.
  • The blog name on line 3.
  • The administrator's email (admin_email) on line 6 and so on.

WordPress uses these options to operate, display content, etc. Sometimes these data can be modified, and sometimes WP manages them alone.

4 basic things to know

Before moving on to the next part, let me make four additional remarks about the WordPress database :

  • The 12 tables developed above are part of the “Core” of your database. This means that they will be here by default on every new DB you create.
  • To store their data, the enhancements you use can use the default tables in your database (for example, table wp_options). However, some plugins create their own tables. This is Yoast SEO‘s and WooCommerce‘s case, to name a few.
Les tables générées par Yoast sur la base de données WordPress
  • You can find the login information for your WordPress database (database name, user, password, database address etc.) in your wp-config.php file.
Where to find your WordPress database login information: in your wp-config.php file
An example of identifiers for a locally created database. Be more creative for an online site 😛
  • A multi-site network's database has a few specificities. Each site has separate tables in the database: 9 of the 12 “native” WordPress tables are duplicated for each site.

All right, are you still there? Now, we're going to dive deeper into the subject.

It's time to learn how to create your database.

Come on, get on board!

How to create a database quickly and easily

For a beginner, the easiest way to create a database on a site ready to go online is to use cPanel, provided your web host offers one.

BlueHost, one of WPMarmite's partners, offers for instance example cPanel.

Your best WordPress projects need the best host!

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

The cPanel is the administration interface of your hosting that allows you to manage a large number of features in a few clicks, and this in a rather visual way.

Among its many features, there is a tool called Softaculous, which allows to install CMS easily (WordPress, for example).

Softaculous pour installer WordPress

Good news for you: no more need to bother with WordPress database creation

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

Pretty cool, right? Now, if you want to struggle with the manual method, don't worry. I have the recipe right underneath.

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

Bases de données MySQL sur CPanel

The process is quite fast. This will involve giving a name to your database, creating an associated user, or granting privileges, i.e. rights to use the database.

For the steps to follow, I detail everything in our How to install WordPress post, a step-by-step guide.

How do I create a WordPress database locally?

Using WordPress locally means that you will place it on your computer, without having to go through the services of a host.

This can be useful to create or redesign a site in all peace of mind, for example.

To design a site – and therefore a database – locally, there are two major options (be careful, don't create a database alone: it's useless).

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

The second is called MAMP. To create a database with MAMP, you will have to ask an old friend: phpMyAdmin. Yeah, back again.

Don't panic, it's simple enough. I'll share how to do it in this post.

Congratulations, your database is now created.

But your job doesn't stop there. In order for your WordPress database to do its best, you'll have to optimize it.

I'll tell you why and especially how to do so in the next part.

How do I manage and optimize a WordPress database?

This is a natural phenomenon common in human beings: as we get older, we tend to put on a few pounds.

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

Ballon gonflable se touche le ventre
Like us, the database can fatten up if we don't keep an eye on the scale…

The problem is that an overloaded database can severely slow down a site.

From a user experience perspective, we've seen better: everybody hates a page that takes ages to display.

For Search Engine Optimization, the impact can also be quite negative

According to Google, the performance of a site is one of the 3 most important factors to take into account for your SEO.

As a result, you don't really have a choice: you're going to have to optimize your WordPress database to stay in good shape. This will allow you to free up storage space on your hosting server, so as not to alter 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.

Review your 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 back pedal. Except that it unnecessarily overloads your database, because WordPress does not impose limits on the number of revisions.

Fortunately, a few lines of code will help to overcome this problem.

To reduce the number of revisions of each article (the number corresponds to the number of revisions kept in the database by content type), use the snippet below in your wp-config.php file

define('WP_POST_REVISIONS', 3 );

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

define( 'WP_POST_REVISIONS', false );

Careful with that last piece of code. If you choose to use it, no further revisions will be made. In case of bad manipulation on your part (e.g. closing the editing window), or a bug in your browser, you will lose all the content created beforehand. As a last resort, it will be possible to restore your content through automatic backup. The link to this snippet is in French, but the code it contains may help you do so.

If you are not tempted to manipulate code, know that you can use the WP Rocket premium plugin.

This cache plugin (a system to speed up the loading of your pages) has an option to optimize your database.

Optimize your WordPress database with WP Rocket

Thanks to it, you'll be able to

  • Clean up revisions and drafts.
  • Clean up the comments.
  • Clean up transients, which allow you to temporarily cache information in your database.
  • Clean the database tables.
  • Schedule an automatic cleaning.

Concerning plugins, here are four that will also allow you to optimize your database (activate only one, though):

Speed up your website with WP Rocket

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

Remove plugins and themes you don't use

Another lever that you can activate is the deletion of themes and plugins that you don't/no longer use.

No need to keep plugins just to garnish your Dashboard, do we agree?

The trick is that it won't be enough to make all their information disappear from your database, while continuing to overload it.

Indeed, some plugins leave tables lying around, even when deleted

In particular, this allows you, if you reactivate them, not to have to set them up again.

For a plugin that you are sure you don't want to use anymore, this is of no interest.

To delete unused tables, you can use the WP-DB Manager enhancement.

L'extension WP-DB Manager

If you plan to paste it manually, you will have to go through phpMyAdmin

This manipulation is not very complex, but I advise you to use it only as a last resort, and by thinking of saving your site beforehand

To get started, go to phpMyAdmin through your host's interface (your cPanel, for example).

Select your WordPress database, top left. All your tables should then appear on the right-hand side of your screen.

Then select the table(s) of your choice and choose Optimize Table (and especially not Empty or Delete, be careful).

Optimize tables in your WordPress database to remove plugins and themes you don't use

Are you still following? Then let's go to the third tip.

Get rid of spam

They can sometimes make us smile, but they are mostly very painful. Did you recognize them? Spam comments, of course.

Real undesirables, those. And if I tell you that on top of that, they're overloading your database, that gives you several good reasons to get rid of them right away.

Among the many anti-spam plugins available on the market, there's one that does the job quite well: Akismet.

Le plugin Akismet pour WordPress

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

You will find it by default as soon as you install WordPress on your administration, because it was designed by Automattic, the WordPress parent company.

So, frankly, don't deny yourself that.

How to secure your database

As you have just seen, optimizing your WordPress database remains 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 safety.

In this section, I'll show you two ways to increase the security of your DB:

  • By saving it
  • By changing the prefix of its tables

How to backup your database using a plugin?

I will detail the manual process later but let's start with the “plugin” method, more accessible if you are not an experienced WP user.

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

To not hide anything from you, I'll warn you: there are dozens and dozens of plugins to achieve your goals

If you ask Alex, he'll advise you to use UpdraftPlus.

Le plugin de sauvegarde UpDraftPlus

It must be said that this plugin has many advantages:

  • You can download your database in one click.
  • You can schedule backups of your WordPress DB and export them to an external service (such as Google Drive).
  • You can schedule automatic backups.
  • You can restore your DB directly from the plugin.

How to backup your database by hand

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

I am thinking for example of the case where your site's administration becomes inaccessible, even momentarily, making it impossible to backup or restore your DB.

To proceed without a plugin, everything happens on phpMyAdmin (again). Go to this tool and select the database of your choice by clicking on it.

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

phpMyAdmin gives you the choice between two export methods: fast or custom (notably to select the tables of your choice)

By default the SQL format is already selected, leave it as it is, and click Go.

Export a WordPress database on phpMyAdmin

A file in SQL format will then download to your computer. You are now in possession of your database: congratulations ;-).

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.

Import a WordPress database

Let's say the process is the same as for export, but in reverse.

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

How do I change the prefix of the database tables

Most of the time, by default when installing WordPress, the wp_ prefix is assigned to the tables in your database.

In terms of security, this is an issus, because it makes your site more vulnerable to SQL attacks from bad hackers and web pirates (not Jack Sparrow type pirates, real pirates).

To cover yourself, get into the habit of changing this famous prefix as soon as you install your next sites.

Change your WP database's prefix

Think about choosing something more complicated, even incomprehensible, instead of wp_ (ex: dgh47yz_) To generate a strong password, you can use this handy site.

If the damage is already done, don't panic. It's still possible to rectify the situation.

The easiest way is to use a plugin. Brozzme DB Prefix & Tools Addons will do the job very well, for example.

Brozzme DB Prefix & Tools Addons

Choose the new prefix of your choice, and that's it. The plugin will take care of updating your DB by itself, as well as the information contained in your wp-config.php file.

On the other hand, absolutely think of backing up your DB before using the plugin. You never know: in case of a bug or connection problem, your site could crash.

Otherwise, you can also do it by hand. But I warn you, it's much riskier

Here are 5 steps to do so summarized below:

Step 1: Backup your site (files + DB).

Step 2: Modify your wp-config.php file by adding the new prefix of your choice (the one starting with wp_).

Add your new database prefix

Step 3: Login to phpMyAdmin, select the database of your choice, and enter the following SQL queries (replace xxx by the new prefix chosen, i.e. by xjh5g7j8_ in my example) :

Rename table wp_commentmeta to xxx_commentmeta;
Rename table wp_comments to xxx_comments;
Rename table wp_links to xxx_links;
Rename table wp_options to xxx_options;
Rename table wp_postmeta to xxx_postmeta;
Rename table wp_posts to xxx_posts;
Rename table wp_termmeta to xxx_termmeta;
Rename table wp_terms to xxx_terms;
Rename table wp_term_relationships to xxx_term_relationships;
Rename table wp_term_taxonomy to xxx_term_taxonomy;
Rename table wp_usermeta to xxx_usermeta;
Rename table wp_users to xxx_users;

On PHPMyAdmin, it looks like this..

Step 4: Modify the wp_options table, as you will no longer have sufficient rights to access the administration of your site. In the option_name column, locate the field wp_user_roles, double-click on it, and replace it with xxx_user_roles.

wp_user_roles sur la BDD

Step 5: Modify the wp_usermeta table. In the meta_key column, replace all old wp_ prefixes with the new ones. For your convenience, you can click on the meta_key column to filter in alphabetical order.

Meta-key de la Base de données WordPress

How to solve the main database errors

Before I let you manage your database as you wish, I want to give you some final advice that you may find useful when you're helpless, that is when your database no longer responds. Unfortunately, this can happen, so you might as well be prepared.

The database connection error

In Shakespeare's language, it's called Error Establishing a Database Connection. Just the sight of it in writing is bad enough.

You will see this nice – what do I say – awful message when your site will no longer be able to connect to its database.

Erreur de connexion à la base de données

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

But the cause may be deeper: it would be far too simple, otherwise.

In this case, you will first need to check 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' give you all the details to do so in WPMarmite's extensive guide to WordPress errors.

How to reset your database

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

This can be handy if you work on a test site, or locally. This saves you from having to reinstall WordPress, for example.

Be careful, do not engage in this practice on a site in production unless you are sure of what to do. Resetting a database means that you will lose all your content. In any case, make a backup before doing anything.

For this, you can use a specific and handy plugin: WP Database Reset.

WP Database Reset

It will allow you, for instance, to reset the entire database, or only certain tables of your choice.

Basic Recap

To ensure its smooth operation, I have presented you with how:

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

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

What about you? How do you manage your DB? Or maybe you prefer not bothering and stay focused on your site?

Feel free to share your tips and advice with WPMarmite readers in the comments below.