A typical WordPress install consists of both files and a database. A Database is like a filing cabinet for your WordPress site. It stores all of your content, including posts, pages, links, users, latest revisions and, comments as well as the specific settings for your site – including themes and plugins. This is a lot of data! So, if you’ve been using WordPress for a while, there is a big chance that your database is filled up with data that you no longer need. It’s time to clean up!
Optimizing your database not only helps to speed up your site so that it loads faster, but it also frees up some significant space in your database, which makes it run more efficiently. The database is a crucial part of your WordPress site – without it, your site doesn’t work. We advise you to take extreme caution when you make changes and to create a backup of your database before making modifications.
Cleaning the options table
Some tables are used every day to make your WordPress-site run, for example, the wp_options table. These tables consist of a lot of data that you eventually need to clean up. The wp_options table stores all your settings including the site URL, the currently used theme, and a list of the active plugins. Also, a lot of plugins and themes, store bits and pieces of information in this table that help rendering the page. When you remove a plugin, it can leave traces in your options table that clutter things up and should be removed. To find out if that is the case, you can search for it manually in the database.
How do you find unused plugins in the database?
It is tricky to know exactly what to search for, as a plugin can be written in a variety of ways. An example is a plugin like ”Really Simple SSL”. The plugin could be called ”really simple ssl”, ”really-simple-ssl”, ”really_simple_ssl”, ”reallysimplessl”, or something completely different in the database. This is how you do:
- Go to phpMyAdmin and select Databases in the top.
- Click the Database-name (for example domain_com), and go to SQL.
- Paste in the following SQL query:
SELECT * FROM `www_options` WHERE `autoload` = `yes` AND `option_name` LIKE `%plugin%`Replace www_ in www_options with the database prefix you have used, and replace plugin in %plugin% with the name of the plugin that you want to remove traces from.
- Press the Go button, it should list all the rows that were left by the specific plugin.
- Remove the rows by pressing the Delete button.
Note: You need to adjust the query to include the table name. Many WordPress installs use wp_ as prefix, and then the options table would be wp_options. If it’s a subdomain (e.g. test.domain.tld), one-click installs will use the folder name as the prefix, e.g.: test_options. In some cases, you should also add a number: test0_options.
How do you remove transient data?
Aside from the plugin and theme-specific data, your database also contains transient data, used to store temporary information in your database. Normally transient data is removed automatically, by the plugin that put it there. However, if you remove the plugin, the transient data that is stored in your database at that time, won’t be removed and remain in the database forever, unless you manually remove it.
The steps in removing transient data are similar to removing plugins. You can easily identify them because they usually have the word transient in their names. You can use the following query to select them:
- Go to phpMyAdmin.
- Paste in the following SQL query:
SELECT * FROM `www_options` WHERE `autoload` = 'yes' AND `option_name` LIKE '%transient%'
- Press the Go button, it should list all the transient rows.
- Remove them by pressing the Delete button.
Alternatively, you can also use a plugin like transients manager to manage your transient data.
The procedures listed above is one of the many tips you can find in optimizing your database. If you like to go beyond, you can also delete the old unused plugin tables (if you have one), delete spam comments, old posts, deleted posts etc. Alternatively, you can also use some plugins to do this for you. Cleaning up your WordPress database is not just an optional thing but rather should be done regularly.
Want to learn more about WordPress?
We have numerous WordPress-guides for you ready in our support section, please have a look if you need inspiration or help.